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_category records. The following fields have BTREE indexes:
contact.id, UNIQUE(contact_category.contact_id,
contact_category.category_id)
SELECT DISTINCT contacts.id FROM contacts
LEFT JOIN contact_category ON contacts.id=contact_category.contact_id
WHERE category_id IN (1,2,3,4)
LIMIT 0,500;
----------------------------------------------------------------------------------------
SELECT DISTINCT contacts.id FROM contacts, contact_category
WHERE contacts.id=contact_category.contact_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