1

I have a simple SQL query that i'm trying to optimise to remove "Using where; Using temporary; Using filesort".

This is the table:

CREATE TABLE `special_offers` (
  `so_id` int(11) NOT NULL auto_increment,
  `so_lid` int(11) NOT NULL,
  `so_product_id` int(11) NOT NULL,
  `so_bonus_product` int(11) NOT NULL,
  `so_reverse_relate` tinyint(1) NOT NULL default '0',
  `so_discount_amount` varchar(6) NOT NULL,
  `so_start` date NOT NULL default '0000-00-00',
  `so_expiry` date NOT NULL default '0000-00-00',
  `so_active` tinyint(1) NOT NULL,
  `so_archived` tinyint(4) NOT NULL default '0',
  `so_added` datetime NOT NULL,
  PRIMARY KEY  (`so_id`),
  KEY `so_archived` (`so_archived`),
  KEY `so_active` (`so_active`),
  KEY `so_start` (`so_start`),
  KEY `so_expiry` (`so_expiry`),
  KEY `so_product_id` (`so_product_id`),
  KEY `so_bonus_product` (`so_bonus_product`),
  KEY `so_lid` (`so_lid`)
) ENGINE=MyISAM AUTO_INCREMENT=65610 DEFAULT CHARSET=latin1

This is the query:

SELECT `so_id` , `so_lid` , `so_bonus_product` , `so_product_id`
FROM `special_offers`
WHERE `so_archived` = '0'
AND `so_active` = '1'
AND (
`so_start` <= CURDATE( )
OR `so_start` = '0000-00-00'
)
AND (
`so_expiry` >= CURDATE( )
OR `so_expiry` = '0000-00-00'
)
GROUP BY `so_lid`

An EXPLAIN:

mysql> EXPLAIN SELECT `so_id` , `so_lid` , `so_bonus_product` , `so_product_id` FROM `special_offers` WHERE `so_archived` = '0' AND `so_active` = '1' AND ( `so_start` <= CURDATE( ) OR `so_start` = '0000-00-00' ) AND ( `so_expiry` >= CURDATE( ) OR `so_expiry` = '0000-00-00' ) GROUP BY `so_lid`;
+----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
| id | select_type | table             | type | possible_keys                            | key         | key_len | ref   | rows | Extra                                        |
+----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | special_offers    | ref  | so_archived,so_active,so_start,so_expiry | so_archived | 1       | const | 7684 | Using where; Using temporary; Using filesort |
+----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
robjmills
  • 18,438
  • 15
  • 77
  • 121
  • If there are multiple records per `so_lid`, this query will return any (random) record per `so_lid` (which satisfies the conditions). Is it what you want? – Quassnoi Jan 24 '11 at 14:34
  • @Quassnoi - you may be onto something there. looking at the code around this (which i didnt write) there appears to be a logic issue with what's happening anyway. – robjmills Jan 24 '11 at 14:48

4 Answers4

2

Create a composite index on (so_archived, so_active, so_lid, so_start, so_end)

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • I did try this and it didnt change anything, would i need to remove the existing indexes? – robjmills Jan 24 '11 at 14:22
  • @seengee: Try to use `FORCE INDEX` and see if the plan changes. Also try collecting the stats (`ANALYZE TABLE special_offers`). Generally, these things are hard to debug without access to actual data. – Quassnoi Jan 24 '11 at 14:30
  • using `FORCE INDEX` did switch the query to `using where` but also pushed execution time from 0.0429 sec to 0.1789 sec. ANALYZE TABLE special_offers tells me "Table is already up to date" – robjmills Jan 24 '11 at 14:35
  • @seengee: um, sorry, messed the index order. Please try now. – Quassnoi Jan 24 '11 at 14:46
  • yep thats got it, query execution the same but now "using where". Thanks! Not convinced the actual logic in the code is correct anyway but this certainly answers my question! – robjmills Jan 24 '11 at 14:53
0

i have one remark, when use a date function like :

so_start` <= CURDATE( )

mysql dont use the index on this field so_start ,

try to enter in server side language a real date

Community
  • 1
  • 1
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
0

I'd have a composite index on ( so_archived, so_active, so_lid ) and add one keyword...

SELECT STRAIGHT_JOIN ...rest of query

SELECT STRAIGHT_JOIN 
      `so_id` , `so_lid` , `so_bonus_product` , `so_product_id` 
  FROM 
      `special_offers` 
  WHERE 
          `so_archived` = '0' 
      AND `so_active` = '1' 
      AND ( `so_start` <= CURDATE( ) OR `so_start` = '0000-00-00' ) 
      AND ( `so_expiry` >= CURDATE( ) OR `so_expiry` = '0000-00-00' ) 
   GROUP BY 
      `so_lid`
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • @Quassnoi, "STRAIGHT_JOIN" is a keyword telling the query optimizer to do the query in the order you've stated. By having the "WHERE" condition matching closer on a matchec composite key should select that index. Also its great for doing joins to other tables where the query optimizer might pick another table just because it has less rows and kills performance. – DRapp Jan 24 '11 at 14:37
  • http://stackoverflow.com/questions/313759/mysql-what-does-straight-join-do-in-this-code – Haim Evgi Jan 24 '11 at 14:41
  • @DRapp still leaves me with “Using where; Using temporary; Using filesort” – robjmills Jan 24 '11 at 14:41
  • @DRapp it seems the straight join is pointless in this context anyway according to the other question linked to – robjmills Jan 24 '11 at 14:43
  • @seengee, sorry it didn't work, but I've had great success with it in so many other queries in the past that have significantly improved performance, thought I'd pass it on. – DRapp Jan 24 '11 at 14:46
  • 1
    @DRapp: `STRAIGHT_JOIN` only forces the order the tables are read in a nested loop. Since there is only one table in the query, it has no effect. The order of predicates in the `WHERE` clause does not affect the query plan. – Quassnoi Jan 24 '11 at 14:51
0

I think the group by may cause you problems. 

SELECT  `so_id` , `so_lid` , `so_bonus_product` , `so_product_id` ... GROUP BY `so_lid`

When grouping by so_lid will the values of so_id , so_bonus_product , so_product_id all be the same for a given so_lid. You may find you get unexpected results. This may also cause optimisation problems.

See this article. http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

Jaydee
  • 4,138
  • 1
  • 19
  • 20