• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Index on date column in Mysql table

 
Carl John
Ranch Hand
Posts: 47
Eclipse IDE Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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



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

The Query is like



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
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
(And you should definitely use PreparedStatement for all your queries - here is why.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic