Index on date column in Mysql table

[Carl John]

Hi All

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-03-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...?

I've asked the very same question in other forum , but i'm not getting any answers from there..So i'm posting the same question here.!

[Martin Vajsar]

Databases generally cannot use indexes if the indexed column is part of an expression. Usually not even if the expression is trivial.

In your case, just compute the first and last day of the quarter you're interested in and use a BETWEEN clause with these days. The result will be the same and it will use the index.

If the date column contains time as well, you cannot use BETWEEN. In this case, you need to compute the first day of the quarter in question and the first day of the immediately following quarter, and use a condition like this:
where date_column >= first_day_of_quarter and date_column < first_day_of_next_quarter(And you should definitely use PreparedStatement for all your queries - here is why.)