• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Index on date column in Mysql table

 
Ranch Hand
Posts: 47
Netbeans IDE Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.!
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.)
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic