left outer join query optimisation

[galorin]

I have tweaked my server variables so that

SELECT BENCHMARK(1000000,1+1);

runs in 0.04 seconds (down from 0.1), but the following query still runs slow.

SELECT quote_vars.variable 
FROM quote_vars 
LEFT OUTER JOIN quote_desc 
ON quote_vars.variable = quote_desc.variable 
AND quote_id = 2226 
WHERE value IS NULL;

Both referenced tables have a key, but when I use EXPLAIN I get the following:

+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | quote_vars | ALL  | NULL          | NULL | NULL    | NULL |    22 |             |
|  1 | SIMPLE      | quote_desc | ALL  | NULL          | NULL | NULL    | NULL | 43670 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+

Is it possible to optimise this query? If so, what can I do different? I don't know if it's even possible to optimise a left outer join query, especially one that takes in so many rows.

 

[colorplane]

Thank you for supplying the [tt]EXPLAIN[/tt]. Your query is probably fine, but your tables are not indexed properly, so any time you search MySQL has to do a full table scan which is what takes so much time.

You should add indexes to your quote_vars and quote_desc tables to improve the performance. If your quote_id field is unique and you never have duplicates, you should use a primary key. If your database should allow for duplicates, you should use a regular index. Using an index for any fields that you want to access the data with will improve your performance. Here is a good tutorial for understanding and choosing the proper indexes:

http://www.databasejournal.com/features/mysql/article.php/1382791

Here are some example queries:

ALTER TABLE quote_vars ADD INDEX id(quote_id),
                       ADD INDEX v(variable);
ALTER TABLE quote_desc ADD INDEX id(quote_id),
                       ADD INDEX v(variable);

 

[galorin]

Ta, I'll add in the indexes and see what it does to the speed of the query.

 

[colorplane]

After reading a bit more carefully, I noticed that you already have an index on your table. That may be useful in some queries, but it isn't used in this one.

In your [tt]EXPLAIN[/tt] you should see your new indexes added as suggested in my previous post under [tt]possible_keys[/tt]. If you add separate indexes on the quote_id, value, and variable fields you can see which indexes MySQL actually uses under [tt]key[/tt].

You should get a very significant increase in performance after adding these additional indexes. Be careful not to over-index your tables, use the information from [tt]EXPLAIN[/tt] to determine which indexes are important and which ones aren't.