improve a SELECT SQL query

My data scheme is really simple, let s say it's about farms

• tableA is the main one, with an important field "is_active" assuming the farm is trusted (kind of)
• tableB is a data storage of serialized arrays about farms statistics

I want to retrieve all data about active farm so I just do something like that:

SELECT * FROM tableA LEFT JOIN tableB ON id_tableA=id_tableB WHERE is_active=1 ORDER BY id_tableA DESC;

Right now the query takes 15 sec to execute straight from a sql shell, for example it I want to retrieve all data from the tableB, like :

 SELECT * FROM tableB ORDER BY id_tableB DESC;

it takes less than 1 sec (approx 1200 rows)...

Any ideas how to improve the original query ?

th开发者_如何学编程x

Create indexes on the keys joing two tables..

check this link, how to create indexes in mysql: http://dev.mysql.com/doc/refman/5.0/en/create-index.html

You'll have to create an index.

You could create the following index:

mysql> create index ix_a_active_id on tableA (id_tableA, is_active);

mysql> create index ix_b_id on tableB (id_tableB);

This first creates an index on BOTH the id + is active variable. The second creates an index on the id for tableB.