faster not using Index????

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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