Very slow joins. Please help.

[LucL]

Hey Guys,

Here are my specs:

1. Linux Redhat
2. MySQL 5.0.41
3. InnoDB Tables

Two tables, campaign_ad and campaign_ads_live

- campaign_ad contains all ads (2.2 Million records).
- campaign_ads_live contains only ad_ids (from campaign_ad.id table)that are "Approved" and "Active". Not that that matters, but the numbers are the same right now (so both tables have 2.2 million records)

I will always be selecting from campaign_ads_live. There are four integer fields all 4 have indexes.

Simple query, "SELECT id FROM campaign_ads_live WHERE content_id=4" takes no time to execute and bounces back 682 results.

Now, "SELECT id FROM campaign_ads_live c1 LEFT JOIN campaign_ad c2 ON c2.id=c1.ad_id WHERE c1.content_id=4"

That takes anywhere from 2.5 to 5 seconds to execute. The results are identical to the first query. It's the join that seems to be slowing it down, but it is using all the keys:

Explain:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------------+---------+--------------------------+------+--------------------------+
| 1 | SIMPLE | c1 | ref | content_index | content_index | 5 | const | 682 | Using where; Using index |
| 1 | SIMPLE | c2 | eq_ref | PRIMARY | PRIMARY | 4 | c1.ad_id | 1 | Using index

I tried analyze and optimize tables and both didn't do much.

I'm sure this should run faster than 2.5 seconds but I have no idea what's wrong.

Thanks!

 

[LucL]

Someone must know. Any words of wisdom are greatly appreciated

 

[KarveR]

show index from tablename

mke sure that the fields you join on are indexed if teh cardinality is high enough, a low cardinality will be a burden to the system.

You could try also:
... LEFT JOIN campaign_ad c2 ON c1.ad_id=c2.id ...

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.