how can I make this SELECT query more efficient?

[bitt3n]

The following query executes in 3.6 seconds on a products table of 475K rows (returning ~1K rows). I would like to make it more efficient.

 

SELECT p.*, shop
FROM products p
JOIN users u ON p.`date` >= u.prior_login and u.user_id = 22
JOIN shops s ON p.shop_id = s.shop_id
ORDER BY shop, `date`, product_id;

 

on the product table I have indexes on (product_id), (date), (product_id,date),(shop_id,date). on the shops table I have an index on (shop_id).

 

If I remove from the ORDER BY clause both date and product_id, the query executes in 0.06 seconds. If I add either of those columns back to the ORDER BY, execution time goes back to 3.6 seconds.

 

I notice if I remove the first JOIN, the query executes in 7.4 seconds (returning all 475K rows). I am running MySQL client version: 5.0.67.

[The Little Guy]

add an explain in front of your select, like this:

 

EXPLAIN SELECT p.*, shop
FROM products p
JOIN users u ON p.`date` >= u.prior_login and u.user_id = 22
JOIN shops s ON p.shop_id = s.shop_id
ORDER BY shop, `date`, product_id;

 

Post the results.

[bitt3n]

add an explain in front of your select, like this:

 

EXPLAIN SELECT p.*, shop
FROM products p
JOIN users u ON p.`date` >= u.prior_login and u.user_id = 22
JOIN shops s ON p.shop_id = s.shop_id
ORDER BY shop, `date`, product_id;

 

Post the results.

 

sure, here it is

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	u	const	PRIMARY,prior_login	PRIMARY	4	const	1	Using temporary; Using filesort
1	SIMPLE	s	ALL	PRIMARY	NULL	NULL	NULL	90	 
1	SIMPLE	p	ref	shop_id,date,shop_id_2,shop_id_3	shop_id	4	db.s.shop_id	4761	Using where

[bitt3n]

I've got a few people speculating that the problem may be the result of the 'temp table size' or 'max heap table size' parameters, or that I need to use a 'covering index'. Are these likely to be related to the problem?

[The Little Guy]

I see that your shops table isn't using an index, though I don't know if that will fix the speed because you say it is faster without the order by.

 

But, an order by is the last thing to run in your code, first it needs to select all the data that it needs, then it needs to go through all that data again to choose the order it needs to return it in, it is almost like running 2 queries, but then again you have 3 orders, so it needs to order it 3 times. order by is a slow operation, and there is nothing via MySQL to speed it up, so if you remove it and order it using php it could speed things up.

[fenway]

You're missing an index on the "s" table.

[bitt3n]

You're missing an index on the "s" table.

someone else observed this -- I'm confused why it says this, since I do have an index on that table.

 

these are the keys on my products table

Keyname Type    Cardinality     Action  Field
PRIMARY  PRIMARY     476122              product_id
shop_id  UNIQUE  476122              shop_id,link
title_2  UNIQUE  476122              title,image
brand    INDEX   8983            brand
title    INDEX   238061              title
date     INDEX   158707              date
shop_id_2    INDEX   90              shop_id
product_id   INDEX   476122          product_id,date
shop_id_3    INDEX   158707          shop_id,date

 

so index named shop_id_2 is the shop_id index. on shops, the index is

Keyname   Type  Cardinality Action  Field
PRIMARY   PRIMARY    90              shop_id

 

this should be ok, right?

 

also here are the SHOW CREATE tables

 

CREATE TABLE `products` (\n  `product_id` int(10) NOT NULL auto_increment,\n  `shop_id` int(10) NOT NULL default '0',\n  `title` varchar(120) NOT NULL default '',\n  `brand` varchar(80) NOT NULL default '',\n  `price` float NOT NULL default '0',\n  `image` varchar(255) NOT NULL default '',\n  `image_height` smallint(6) default NULL,\n  `image_width` smallint(6) default NULL,\n  `link` varchar(255) NOT NULL default '',\n  `date` datetime NOT NULL default '0000-00-00 00:00:00',\n  PRIMARY KEY  (`product_id`),\n  UNIQUE KEY `shop_id` (`shop_id`,`link`),\n  UNIQUE KEY `title_2` (`title`,`image`),\n  KEY `brand` (`brand`),\n  KEY `title` (`title`),\n  KEY `date` (`date`),\n  KEY `shop_id_2` (`shop_id`),\n  KEY `product_id` (`product_id`,`date`),\n  KEY `shop_id_3` (`shop_id`,`date`)\n) ENGINE=MyISAM AUTO_INCREMENT=547387 DEFAULT CHARSET=latin1

 

CREATE TABLE `shops` (\n  `shop_id` int(10) NOT NULL auto_increment,\n  `shop` varchar(80) NOT NULL default '',\n  `last_look` datetime NOT NULL default '0000-00-00 00:00:00',\n  PRIMARY KEY  (`shop_id`)\n) ENGINE=MyISAM AUTO_INCREMENT=142 DEFAULT CHARSET=latin1

[fenway]

I didn't realize that was the the primary index -- are you really returning that many rows?