# faster not using Index????

**URL:** <https://forums.percona.com/t/faster-not-using-index/174>\
**Category:** Other MySQL® Questions\
**Created:** [January 17, 2007, 11:43am UTC](https://forums.percona.com/t/faster-not-using-index/174 "2007-01-17T11:43:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mysted](https://avatars.discourse-cdn.com/v4/letter/m/c0e974/32.png) [@mysted](https://forums.percona.com/u/mysted)\
**Post date:** [January 17, 2007, 11:43am UTC](https://forums.percona.com/t/faster-not-using-index/174/1 "2007-01-17T11:43:47Z")

</div>

One table VAL\_FAKTA\_VH containing 5.000.000 rows and one table VAL\_DIM\_AVTAL containing 34 rows. The query takes 15 s using Index and 7 s not using index, how is this possible!!! It is not a question about IO, no IOWAIT but alot of CPU 99% (on machines with single CPU and 49.9 om machines with 2 CPU, MySQL does not seems to utilize both CPU:s)

HW  
2\*2Ghz CPU  
8G RAM

I have used huge-conf with the following add:  
join\_buffer\_size 131072  
key\_buffer\_size 3221225472  
tmp\_table\_size 67108864  
read-only  
and some more

DB:  
VAL\_FAKTA\_VH.MYD ~ 600M  
VAL\_FAKTA\_VH.MYI ~ 400M  
VAL\_DIM\_AVTAL.MYI ~ 2M  
VAL\_DIM\_AVTAL.MYD ~ 1M

mysql\> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL\_FAKTA\_VH V force index(Index\_\_avtalid) , VAL\_DIM\_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = ‘Huvud’ group by avtal.avtal;  
±—±------------±------±-----±---------------±------- --------±--------±--------------------±-------±--------- —+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±-----±---------------±------- --------±--------±--------------------±-------±--------- —+  
| 1 | SIMPLE | avtal | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where |  
| 1 | SIMPLE | V | ref | Index\_\_avtalid | Index\_\_avtalid | 5 | carro.avtal.avtalid | 138929 | Using where |  
±—±------------±------±-----±---------------±------- --------±--------±--------------------±-------±--------- —+  
2 rows in set (0.00 sec)  
This query takes 15 s

If I ignore index:  
mysql\> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL\_FAKTA\_VH V ignore index(Index\_2, Index\_\_avtalid) , VAL\_DIM\_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = ‘Huvud’ group by avtal.avtal;  
±—±------------±------±-------±--------------±------ --±--------±----------------±--------±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±-------±--------------±------ --±--------±----------------±--------±------------+  
| 1 | SIMPLE | V | ALL | NULL | NULL | NULL | NULL | 4723596 | |  
| 1 | SIMPLE | avtal | eq\_ref | PRIMARY | PRIMARY | 4 | carro.V.avtalid | 1 | Using where |  
±—±------------±------±-------±--------------±------ --±--------±----------------±--------±------------+  
2 rows in set (0.00 sec)

mysql\> SELECT avtal.avtal, sum(utfallkronor) FROM VAL\_FAKTA\_VH V ignore index(Index\_2, Index\_\_avtalid) , VAL\_DIM\_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = ‘Huvud’ group by avtal.avtal;  
±------±------------------+  
| avtal | sum(utfallkronor) |  
±------±------------------+  
| Huvud | 20396597.210337 |  
±------±------------------+  
1 row in set (7.98 sec)

I have tried this on several machines with different architectures and different configurations the result is the same … please help me with this problem?

/Ted

---

<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:** [January 17, 2007, 12:03pm UTC](https://forums.percona.com/t/faster-not-using-index/174/2 "2007-01-17T12:03:28Z")

</div>

The index is secondary in this case what is important is join order, which you can force with STRAIGHT\_JOIN hint by the way.

Note the order of tables becomes different and this is why.

Scanning large table and doing single row lookups in tiny tables is faster than other way around. Quite expected.

---

<div class="post-metadata">

**Author:** ![mysted](https://avatars.discourse-cdn.com/v4/letter/m/c0e974/32.png) [@mysted](https://forums.percona.com/u/mysted)\
**Post date:** [January 18, 2007, 4:11am UTC](https://forums.percona.com/t/faster-not-using-index/174/3 "2007-01-18T04:11:42Z")

</div>

Thanks for your answer!

With straight\_join the question takes 7 s, but if you always use straight\_join other questions suffer.  
Should not the optimizer se this and do the appropriate thing in this case? Can the optimizer learn from earlier queries?

Background to problem:  
We will be creating this kind of questions from our Cognos-platform and are kean not to build these kind of exceptions in to our model. The Cognos environment are to be exposed to 1000 end users and these questions are generated by Cognos. In our model now with SQL Server this is not a problem.

Is there a way to get this to work with MySQL? HW, changing the structure of data etc. We are aiming for 2-3 seconds for this query.

Best regards,  
Ted