Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

left outer join query optimisation

Status
Not open for further replies.

galorin

MIS
Nov 22, 2007
154
GB
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.
Code:
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:
Code:
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| 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.
 
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:

Here are some example queries:

Code:
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);
 
Ta, I'll add in the indexes and see what it does to the speed of the query.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top