1k entries query with multiple JOIN's takes up to 10 seconds

Here's a simplified version of the structure (left out some regular varchar cols)开发者_开发百科:

CREATE TABLE `car` (
 `reg_plate` varchar(16) NOT NULL default '',
 `type` text NOT NULL,
 `client` int(11) default NULL,
 PRIMARY KEY  (`reg_plate`)
)

And here's the query I'm trying to run:

SELECT * FROM (
SELECT 
    car.*,
    tire.id as tire,
    client.name as client_name
FROM 
    car
        LEFT JOIN client ON car.client = client.id
        LEFT JOIN tire ON tire.reg_plate = reg_plate
GROUP BY car.reg_plate
) t1

The nested query is necessary due to the framework sometimes adding WHERE / SORT clauses (which assume there are columns named client_name or tire). Both the car and the tire tables have approx. 1,5K entries. client has no more than 500, and for some reason it still takes up to 10 seconds to complete (worse, the framework runs it twice, first to check how much rows there are, then to actually limit to the requested page)

I'm getting a feeling that this query is very inefficient, I just don't know how to optimize it.

Thanks in advance.

First, read up on MySQL's EXPLAIN syntax.

You probably need indexes on every column in the join clauses, and on every column that your framework uses in WHERE and SORT clauses. Sometimes multi-column indexes are better than single-column indexes.

Your framework probably doesn't require nested queries. Unnesting and creating a view or passing parameters to a stored procedure might give you better performance.

For better suggestions on SO, always include DDL and sample data (as INSERT statements) in your questions. You should probably include EXPLAIN output on performance questions, too.