0

I have a big table with many many tuples, however I perform some queries just in a small range of this (extracted by date range), I would like to know how to set up the table struture to optimize the db work on date range. May I add startdate and enddate fields as index? Can it helps?

Tobia
  • 9,165
  • 28
  • 114
  • 219
  • Do you have a date field? Or multiple date fields? Ie, does the record represent a range of dates, or just a single date? – Kickstart Apr 30 '13 at 08:57
  • I have a range date so two fields: startdate, enddate – Tobia Apr 30 '13 at 08:59
  • Yes an index on those 2 columns would likely help (although possibly on those 2 columns plus others depending on what else you are searching on). – Kickstart Apr 30 '13 at 09:08
  • As general rule can we say that an important (for selection) field or fields in "where" condition shuld be an index? – Tobia Apr 30 '13 at 10:17
  • 1
    Yes. But MySQL will ignore any index which doesn't narrow down the results sufficiently and it will only use a single index on a table in a query. Ie, if you had a table of people with a column for birth town and another for the town they live in with an index on each, if you queried specifying both towns MySQL would only use one of the indexes. Hence you would probably want an index that covers both columns it that was a common query. – Kickstart Apr 30 '13 at 10:22

0 Answers0