Optimizing single-row queries from large tables in MySQL

Question

I am dealing with MySQL tables that are essentially results of raytracing simulations on a simulated office room with a single venetian blind. I usually need to retrieve the simulation's result for a unique combination of time and blind's settings. So I end up doing a lot of

SELECT result FROM results WHERE timestamp='2005-05-05 12:30:25' \
         AND opening=40 AND slatangle=60

This looks suspiciously optimizable, since this query should never ever return more than one row. Does it make sense to define an index on the three columns that uniquely identify each row? Or are there other techniques I can use?

Answer 1

The answer is most definately a yes. If you define a unique index on timestamp, opening and slatangle MySQL should be able to find your row with very few disc seeks.

You might experiment with creating an index on timestamp, opening, slateangle and result. MySQL may be able to fetch your data from the index without touching the datafile at all.

The MySQL Manual has a section about optimzing queries.

Answer 2

I wouldn't suggest adding 3 indexes. An index using all three columns may be better and even setting the primary key unique on that combination would be best - only if you're sure that it unique.

Answer 3

I would suggest adding LIMIT 1; to the end of the query.

William

Answer 4

Yes, create an index of multiple columns helps. Also you should test the performance of different column order, ie O(c1, c2, c3) != O(c2, c1, c3)

Have a look

http://joekuan.wordpress.com/2009/01/23/mysql-optimize-your-query-to-be-more-scalable-part-12/ http://joekuan.wordpress.com/2009/01/23/mysql-optimize-your-query-to-be-more-scalable-part-22/