# Index optimization in Join

**URL:** <https://forums.percona.com/t/index-optimization-in-join/215>\
**Category:** Other MySQL® Questions\
**Created:** [February 27, 2007, 4:36am UTC](https://forums.percona.com/t/index-optimization-in-join/215 "2007-02-27T04:36:38Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![enzuccio](https://avatars.discourse-cdn.com/v4/letter/e/d9b06d/32.png) [@enzuccio](https://forums.percona.com/u/enzuccio)\
**Post date:** [February 27, 2007, 4:36am UTC](https://forums.percona.com/t/index-optimization-in-join/215/1 "2007-02-27T04:36:38Z")

</div>

Hi,

congratulations for your tutorials, very interesting. I need assistance with some troubles.

I need to optimize a join in order to speed up my site.

The join is:

SELECT scat.subcatid, scat.catid, COUNT(\*) as adcnt  
FROM clf\_ads a  
INNER JOIN clf\_subcats scat ON scat.subcatid = a.subcatid AND a.enabled = ‘1’ AND a.verified = ‘1’ AND a.expireson \>= NOW()  
INNER JOIN clf\_cats cat ON cat.catid = scat.catid  
INNER JOIN clf\_cities ct ON a.cityid = ct.cityid  
WHERE scat.enabled = ‘1’  
GROUP BY a.subcatid

Query took about 0.8142 sec and in my opinion it is too much slow.

Here the explain:

table type possible\_keys key key\_len ref rows Extra  
a ref subcatid,cityid,verified,enabled verified 1 const 22614 Using where; Using temporary; Using filesort  
scat eq\_ref PRIMARY,catid PRIMARY 4 a.subcatid 1 Using where  
cat eq\_ref PRIMARY PRIMARY 4 scat.catid 1 Using index  
ct eq\_ref PRIMARY PRIMARY 4 a.cityid 1 Using where; Using index

Here info about the table “a”:

Keyname TypeCardinality Action Field  
PRIMARY PRIMARY 23895 adid  
subcatid INDEX 82 subcatid  
cityid INDEX 102 cityid  
verified INDEX 2 verified  
enabled INDEX 2 enabled

I tried to avoid “Using temporary; Using filesort” on a creating an index for “a” on verified-cityid-subcatid but the performance is the same and “Using temporary; Using filesort” is switched to the table ct.

Please can you help me?

Thanks!

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [February 27, 2007, 8:04am UTC](https://forums.percona.com/t/index-optimization-in-join/215/2 "2007-02-27T08:04:29Z")

</div>

To avoid filesort and temporary table index needs to be something like

(enabled,verified,subcatid)

---

<div class="post-metadata">

**Author:** ![enzuccio](https://avatars.discourse-cdn.com/v4/letter/e/d9b06d/32.png) [@enzuccio](https://forums.percona.com/u/enzuccio)\
**Post date:** [February 27, 2007, 8:22am UTC](https://forums.percona.com/t/index-optimization-in-join/215/3 "2007-02-27T08:22:15Z")

</div>

Thanks Peter,

with an index (enabled,verified,subcatid) on “a” now I obtain

* * *

scat ALL PRIMARY,catid NULL NULL NULL 83 Using where; Using temporary; Using filesort

a ref subcatid,cityid,verified,enabled,opt1 opt1 6 const,const,scat.subcatid 145 Using where

cat eq\_ref PRIMARY PRIMARY 4 scat.catid 1 Using index

ct eq\_ref PRIMARY PRIMARY 4 a.cityid 1 Using where; Using index

* * *

Seems that it is not possible to perform the query without “Using temporary; Using filesort”.

Please let me know.

Thanks,

VV

---

<div class="post-metadata">

**Author:** ![jenda](https://avatars.discourse-cdn.com/v4/letter/j/da6949/32.png) [@jenda](https://forums.percona.com/u/jenda)\
**Post date:** [March 6, 2007, 4:23pm UTC](https://forums.percona.com/t/index-optimization-in-join/215/4 "2007-03-06T16:23:39Z")

</div>

Hi all, i also have some problem of this kind. Do you think there is a way to optimize this query ?

SELECT id, nazev, helpid FROM params\_titles WHERE id IN (16,17,18) ORDER BY ord;

I created these indexes: PRIMARY, (`id`, `ord`)

EXPLAIN said:

id: 1  
select\_type: PRIMARY  
table: params\_titles  
type: ALL  
possible\_keys: NULL  
key: NULL  
key\_len: NULL  
ref: NULL  
rows: 3  
Extra: Using where; Using filesort

The keys specified in the IN clause are automatically inserted by calling script and are always different.

What worries me the most is ‘Using filesort’.

I personally think that it is impossible to optimize queries containing the IN clause, but if you are aware of some sort of workaround please let me know, I would really appreciate.

Thanks a lot,

Jan

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [March 6, 2007, 4:37pm UTC](https://forums.percona.com/t/index-optimization-in-join/215/5 "2007-03-06T16:37:46Z")

</div>

Right. I if you have IN MySQL will not use second key part for order by.

Search out blog, I’ve posted on this problem - somethimes you can do good but using set of ordered unions with global order by (assuming you have LIMIT)