can u please do
show index for ad;
show index for tag_ad_map;
and past the output. I am not able to understand the index setup on these
two tables.
regards
anandkl
On 9/15/08, Johan Thorvaldsson <johan.t@stripped> wrote:
>
> My query dont use the indexes for the 2 tables that I have joined. Is there
> someone nice and helpful that could help me optimize this query and make it
> use the indexes?
>
> Query:
>
> SELECT SQL_NO_CACHE COUNT(*) antal, ad.ad_url, ad.adtext_plain FROM ad
>
> LEFT JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id
>
> WHERE tm.tag_id IN (99, 10807, 20728, 447, 807)
>
> AND ad.is_removed = 0 AND ad.is_active=1
> AND (ad.ant_feedback_alert <= 5 OR ad.whitelisted = 1)
> AND tm.is_active=1 AND ad.ad_id != 13687
> GROUP BY ad.ad_id ORDER BY antal DESC LIMIT 10;
>
> Explain version:
> 1 SIMPLE tm range is_active,ad_id,tag_id tag_id 4
> NULL 15353 Using where; Using temporary; Using filesort
> 1 SIMPLE ad eq_ref
> PRIMARY,ad_id,is_removed,is_active,whitelisted,ant_feedback_alert
> PRIMARY 4 rubbt.tm.ad_id 1 Using where
>
> Indexes:
> ad 0 PRIMARY 1 ad_id A 18679 NULL NULL
> BTREE
> ad 1 is_removed 1 is_removed A 1
> NULL NULL BTREE
> ad 1 is_active 1 is_active A 1
> NULL NULL BTREE
> ad 1 source_id 1 source_id A 3
> NULL NULL YES BTREE
> ad 1 whitelisted 1 whitelisted A 1
> NULL NULL YES BTREE
> ad 1 ant_feedback_alert 1 ant_feedback_alert A
> 1 NULL NULL YES BTREE
> ad 1 dt_pub 1 dt_pub A 3113 NULL NULL
> BTREE
> ad 1 dt_updated 1 dt_updated A 3113
> NULL NULL BTREE
> ad 1 ad_url 1 ad_url A 18679 NULL NULL
> BTREE
> ad 1 adtext_plain 1 adtext_plain NULL 1
> NULL NULL FULLTEXT
>
>
> ---------------------------------------------------------------------
> Montania System AB
> Halmstad
> http://www.montania.se
>
> Johan Thorvaldsson
> johan.t@stripped
>
> Kristinebergsvägen 17, S-30241 HALMSTAD
> Telefon +46(0)35-13 68 00 | Fax +46(0)35-13 68 01
>
>
