MySQL Index on date field

Question

I'm using MySQL database. In that i'm having sales datas in one table and i've created an index for the Date column (i.e) OrderedDate.

The datas are retrieved fast when we use the query like

SELECT CustID
       ,CustName
       ,CustPhone 
FROM   SalesData 
WHERE  OrderDate BETWEEN '2012-01-08' AND '2012-05-08';

But for taking the details for Particular Quarter cycle , it gets slow and scans whole table datas..

The Query is like

 SELECT CustID
        ,CustName
        ,CustPhone 
 FROM   SalesData 
 WHERE  Quarter(OrderDate)  = Quarter('2012-01-08') 
        AND Year(OrderDate) = Year('2012-01-08');

Is there anyway to index the quarter function (or) any way to speedup the data retrieval for quarter cycle...?

Explain statement:

For 1st Query

id  Selecttype   table       type    possible_keys   key  key_len  ref   rows  Extra
  1  SIMPLE    SalesData      range    csrv          csrv   4             138    Using where

For 2nd Query (scanning 104785 rows data)

id  Selecttype   table       type    possible_keys   key  key_len  ref   rows  Extra
  1  SIMPLE    SalesData     All                                       104785    Using where

Could you post the explain statement output for both queries here it would help with the answer.... — Namphibian, Jul 08 '13 at 06:58

score 0 · Answer 1 · answered Jul 08 '13 at 07:50

There is no way to index anyFunction(OrderDate) in mysql, unless your store it separately.

I think you can handle it more efficient in 2 ways:

1- Migrate from MySQL to Mariadb, it has Computed/Virtual columns that you can make virtual field as OrderDateQuarter and OrderDateYear also you can index them without much overhead. (MariaDB is a community-developed fork of the MySQL, Ithink it much better than native MySQL)

2- You can store OrderDateQuarter and OrderDateYear in another columns, then index them, you can make it easy by write a trigger to store these two columns OrderDate.

score 0 · Accepted Answer · answered Jul 08 '13 at 08:15

0

You found the solution yourself, why don't you use it?

Just calculate the boundary dates for the quarter in question and use them with BETWEEN:

SELECT CustID
      ,CustName
      ,CustPhone 
FROM   SalesData 
WHERE  OrderDate BETWEEN '2012-01-01' AND '2012-03-31';

You can calculate the boundary dates in your application, or also via MySQL as shown in this article:

http://use-the-index-luke.com/sql/where-clause/obfuscation/dates?db_type=mysql

answered Jul 08 '13 at 08:15

Markus Winand

8,371
1
35
44

Thanks for your suggestion. I calculated the quarterly dates and used them with 'BETWEEN' .. – CarlJohn Jul 08 '13 at 11:26

MySQL Index on date field

2 Answers2