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.

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.