Slow Query W/ Join & Ordering

I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default Ɔ',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:



CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:


SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:



SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:



mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

• Slow Execution For A Left Outer Join Query
• Ordering By The Product Of A Left Join
• Join Too Slow
• Slow Join On Large Tables
• Slow Join On Otherwise Fast Views?
• Ordering Query
• Stop Query Ordering
• Quesion On Ordering In A GROUPED Query
• How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
• Slow Query Log
• Slow Query Using NOT IN
• Slow Query
• Why Is This Query Too Slow?
• Slow Query->Efficient Query
• Slow Query Log Not Staying On
• View Slow Query Log
• Slow Query Log Files
• Query Execution Too Slow!
• Recycling The Slow-query.log
• Slow Update Query
• Slow Query Execution
• Slow Query Issue
• Query With 3.3million Rows Is Slow?
• Server Slow When Exec Following Query
• Slow Query Wile Using Indexes
• Federated Tables Slow? (like 4.5 Hrs For A Query)
• Slow Inefficent Query (FIXED)
• Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
• Turning On Slow Query Logging?
• Help Rewriting A Slow Phpbb Query
• Can't Turn On Slow Query Logging
• 815,000+ Records, DB Table Query Too Slow
• Do Parallel Inserts And Selects Slow Down A Query?
• Large Table, Slow Query Question
• Speed Up Query, Order By Column Is Too Slow!
• Slow Query Execution With Strange Issue
• Slow SELECT Query INNODB Table
• VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
• Why Does The Slow Query Log Show More Rows Than Exist?
• Self Join Query
• Join-query
• Using Join In A SQL Query
• Wish To Join A Query
• Sub Query In An Inner Join
• Using Both JOIN And AVG In One Query
• Query: Inner Join Bug
• Help With A Query Join?
• Using 'if' In A 'join Query' ?
• JOIN Query Works With 3.23 But Not 4.0.13
• Query Involving JOIN
• Join Query By Date
• MySQL JOIN Query