Optimising a date query in MySQL

I have a series of tables which have MySQL date and time attributes where each table can consist of thousands of rows. I need to be able to read these tables interactively so as to retrieve these rows satisfying a date range. So my SQL might be:

select column_1, column_2 from table_name where createddate between '2011-01-01' and '2011-01-31';

Some queries may also work from time p on day x, to time q on day z.

The dates are of MySQL type DATE, and times are type TIME.

开发者_JAVA百科

Does anyone know what might be a good way to optimise such queries? I can create indexes on the date and time columns, use a TIMESTAMP as an alternative etc.

Thanks

Mr Morgan.

---

If you have indexes on your date and time columns, MySQL should optimize these queries.

You can confirm that's actually happening with the SQL EXPLAIN command, see: http://dev.mysql.com/doc/refman/5.0/en/explain.html

---

I would recommend indexes on the DATE columns

ALTER TABLE table_1 ADD KEY (date_col_1);

You may not need indexes on the TIME columns. If you have relatively few rows per date, I would skip this as it's probably just un-needed overhead.