Optimise query to remove "Using where; Using temporary; Using filesort"

Question

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 |
+----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+

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
– Quassnoi, Commented Jan 24, 2011 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
– robjmills, Commented Jan 24, 2011 at 14:48

Quassnoi · Accepted Answer · 2011-01-24 14:46:06Z

2

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

edited Jan 24, 2011 at 14:46

answered Jan 24, 2011 at 14:21

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

robjmills Over a year ago

I did try this and it didnt change anything, would i need to remove the existing indexes?

Quassnoi Over a year ago

@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.

robjmills Over a year ago

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"

Quassnoi Over a year ago

@seengee: um, sorry, messed the index order. Please try now.

robjmills Over a year ago

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!

Community · Accepted Answer · 2017-05-23 11:55:29Z

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

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Jan 24, 2011 at 14:21

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

2 Comments

Quassnoi Over a year ago

CURDATE() is not a field, it's a function.

Quassnoi Over a year ago

MySQL is able to use an index for this predicate.

Jaydee · Accepted Answer · 2011-01-24 14:37:49Z

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

answered Jan 24, 2011 at 14:37

Jaydee

4,1481 gold badge21 silver badges20 bronze badges

Comments

DRapp · Accepted Answer · 2011-01-24 14:38:53Z

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`

edited Jan 24, 2011 at 14:38

answered Jan 24, 2011 at 14:30

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

6 Comments

DRapp Over a year ago

@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.

Haim Evgi Over a year ago

stackoverflow.com/questions/313759/…

robjmills Over a year ago

@DRapp still leaves me with “Using where; Using temporary; Using filesort”

robjmills Over a year ago

@DRapp it seems the straight join is pointless in this context anyway according to the other question linked to

Quassnoi Over a year ago

@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.

|

Collectives™ on Stack Overflow

Optimise query to remove "Using where; Using temporary; Using filesort"

4 Answers 4

5 Comments

2 Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related