optimization question (indexes and joins)

[Alex Glass]

I am building a query to select contacts and filter by category. I was
wondering if anyone could explain why the first select statement takes 950
times as long to execute compared to the second. I'm sure it has something
to do with the way the join is processed but I'm still curious what the
engine actually does in each case. In my test the first statement executed
in 0.03 seconds while the second took

Some additional info- In the tests there were 3911 contact records and 17164
contact_categor y records. The following fields have BTREE indexes:
contact.id, UNIQUE(contact_ category.contac t_id,
contact_categor y.category_id)

SELECT DISTINCT contacts.id FROM contacts
LEFT JOIN contact_categor y ON contacts.id=con tact_category.c ontact_id
WHERE category_id IN (1,2,3,4)
LIMIT 0,500;

----------------------------------------------------------------------------------------

SELECT DISTINCT contacts.id FROM contacts, contact_categor y
WHERE contacts.id=con tact_category.c ontact_id AND category_id IN (1,2,3,4)
LIMIT 0,500;

----------------------------------------------------------------------------------------

Any tips or clarification on the queries or suggestions how to improve them
or the indexes would be greatly appreciated.

Alex

[hoonew]

The primary difference is the type of joins being performed. The first
query is (obviously) a left join, using indexed columns from each table
as the join condition. The second, slower, query is an inner join,
which, according to the docs:

INNER JOIN and , (comma) are semantically equivalent in the absence of
a join condition: both produce a Cartesian product between the specified
tables (that is, each and every row in the first table are joined to all
rows in the second table).

which is then (presumably) joining on some non-indexed columns, and,
save the limit clause, would return 67,000,000+ records as opposed to
3911 in the first query.

An EXPLAIN SELECT is very helpful in determining where index and query
optimization should take place.

Alex Glass wrote:

I am building a query to select contacts and filter by category. I was
wondering if anyone could explain why the first select statement takes 950
times as long to execute compared to the second. I'm sure it has something
to do with the way the join is processed but I'm still curious what the
engine actually does in each case. In my test the first statement executed
in 0.03 seconds while the second took

Some additional info- In the tests there were 3911 contact records and 17164
contact_categor y records. The following fields have BTREE indexes:
contact.id, UNIQUE(contact_ category.contac t_id,
contact_categor y.category_id)

SELECT DISTINCT contacts.id FROM contacts
LEFT JOIN contact_categor y ON contacts.id=con tact_category.c ontact_id
WHERE category_id IN (1,2,3,4)
LIMIT 0,500;

----------------------------------------------------------------------------------------

SELECT DISTINCT contacts.id FROM contacts, contact_categor y
WHERE contacts.id=con tact_category.c ontact_id AND category_id IN (1,2,3,4)
LIMIT 0,500;

----------------------------------------------------------------------------------------

Any tips or clarification on the queries or suggestions how to improve them
or the indexes would be greatly appreciated.

Alex

[Alex Glass]

Thanks for the reply, but if you re-read what I wrote the bizarre thing that
is happening is that the INNER JOIN (Cartesian product) is faster that the
LEFT JOIN which is what I don't quite understand. It seems counter-intuitive
that this be the case...

"hoonew" <ho****@ino.now > wrote in message
news:tlzEe.1169 31$gc6.54828@ok epread04...

The primary difference is the type of joins being performed. The first
query is (obviously) a left join, using indexed columns from each table as
the join condition. The second, slower, query is an inner join, which,
according to the docs:

INNER JOIN and , (comma) are semantically equivalent in the absence of a
join condition: both produce a Cartesian product between the specified
tables (that is, each and every row in the first table are joined to all
rows in the second table).

which is then (presumably) joining on some non-indexed columns, and, save
the limit clause, would return 67,000,000+ records as opposed to 3911 in
the first query.

An EXPLAIN SELECT is very helpful in determining where index and query
optimization should take place.

Alex Glass wrote:

I am building a query to select contacts and filter by category. I was
wondering if anyone could explain why the first select statement takes
950 times as long to execute compared to the second. I'm sure it has
something to do with the way the join is processed but I'm still curious
what the engine actually does in each case. In my test the first
statement executed in 0.03 seconds while the second took

Some additional info- In the tests there were 3911 contact records and
17164 contact_categor y records. The following fields have BTREE indexes:
contact.id, UNIQUE(contact_ category.contac t_id,
contact_categor y.category_id)

SELECT DISTINCT contacts.id FROM contacts
LEFT JOIN contact_categor y ON contacts.id=con tact_category.c ontact_id
WHERE category_id IN (1,2,3,4)
LIMIT 0,500;

----------------------------------------------------------------------------------------

SELECT DISTINCT contacts.id FROM contacts, contact_categor y
WHERE contacts.id=con tact_category.c ontact_id AND category_id IN
(1,2,3,4)
LIMIT 0,500;

----------------------------------------------------------------------------------------

Any tips or clarification on the queries or suggestions how to improve
them or the indexes would be greatly appreciated.

Alex

[hoonew]

Indeed, I need to read more carefully... What does EXPLAIN SELECT return?

Alex Glass wrote:

Thanks for the reply, but if you re-read what I wrote the bizarre thing that
is happening is that the INNER JOIN (Cartesian product) is faster that the
LEFT JOIN which is what I don't quite understand. It seems counter-intuitive
that this be the case...