I would like to speed a MySQL query that basically retrieve a page of data following the pattern below
select
my_field_A,
my_field_B
where
time_id >= UNIX_TIMESTAMP('1901-01-01 00:00:00') AND
time_id < UNIX_TIMESTAMP('2009-01-16 00:00:00')
The field time_id is an MySQL index, yet, the query behaves as if the entire database was read at each query (retrieving a couple of lines being already quite slow). I not an expert in MySQL. Can someone guess what I am doing wrong?
Make sure you have an index (B-tree) on time_id, this should be efficient for range queries. Also make sure that time_id is in the appropriate time format.
If you really want to understand what mysql is doing you can add the keyword 'explain' infront of the query and run it in your mysql client. This will show some information about what mysql is doing and what kind of scans are performed.