mysql between two dates index

Question

I'm selecting Data from a mysql database table. When i'm searching a specific date, my index is wiorking fine. When I'm searching data between two dates the index isn't working and the query takes to long. Has anybody an idea how can i improve the query or the index?

Query A:

EXPLAIN SELECT * FROM sal_import WHERE dateStats>="2011-07-28" AND dateStats<="2011-07-30" GROUP BY f_shop id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE i range dateStats dateStats 3 (NULL) 7896 Using where; Using temporary; Using filesort

Query B:

EXPLAIN SELECT * FROM sal_import i WHERE dateStats="2011-07-30" GROUP BY f_shop id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE i ref dateStats dateStats 3 const 2182 Using where

This is the index of the table:

ALTER TABLE sal_import ADD INDEX(dateStats,f_shop);

Thank you very much.

when i remove the group by clause, the index is working fine. is it a godd solution to group the values in php? — user954740
– user954740, Commented Sep 28, 2011 at 13:57

Pål Brattberg · Accepted Answer · 2011-09-28 10:51:10Z

1

Try this:

SELECT * FROM sal_import WHERE dateStats BETWEEN '2011-07-28' AND '2011-07-30'

Also see http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

answered Sep 28, 2011 at 10:51

Pål Brattberg

4,6981 gold badge32 silver badges41 bronze badges

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

6 Comments

user954740 Over a year ago

hi, i thought the between function was my problem at first, thats why i changed it to the current query. It means the explain function tells me the same problem on between.

satnhak Over a year ago

-1 The reason is that it's the wrong type of index. BETWEEN is just syntactical sugar.

user954740 Over a year ago

What does it mean "wrong type of index" ? When the index is not multiple, i have the same problem.

satnhak Over a year ago

See my answer below. The date column is a secondary index, which means it is not sequentially ordered; thus it can be used for lookups, but not range searches.

satnhak Over a year ago

*well it can, but it requires the data to be pieced back together again, rather than reading sequentially from disk. If there are a small number of records the full table scan might be faster than using the secondary index.

|

satnhak · Accepted Answer · 2011-09-28 12:39:06Z

The reason is that the date index is non-clustered and as such the data is not sequentially ordered by that key on disk. Your table has a small number of records, so the query optimiser has decided it is quicker to do a full table scan, rather than use the secondary index and piece the records together.

A table can only have one clustered index and this is usually the primary key. A date column is generally a valid and sensible column on which to put the clustered index, but it may not be the best use of it; it really depends on how that table is used.

http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx

Unfortunately in MySQL a clustered index must be on a unique column and you cannot define a clustered index on a column (apparently); the database chooses that index for you by default. Given this you might not be in luck on this one.

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

Collectives™ on Stack Overflow

mysql between two dates index

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related