I'm having the following problem with the IGNORE INDEX()/USE INDEX() directives on a
mysql 3.23.32
mysql> explain SELECT age_0,reliable,COUNT(*) FROM age,reliable WHERE age.id=rel
iable.id GROUP BY age_0,reliable;
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extr
a |
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
| age | index | dd_idx,id | dd_idx | 4 | NULL | 1000000 | Usin
g index; Using temporary |
| reliable | eq_ref | dd_idx,id | id | 3 | age.id | 1 |
|
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
Normally my query uses index(dd_idx) on table age_0 and index(id) on table reliable.
Now I want to force the use of index(id) on table age_0 as well:
mysql> explain SELECT age_0,reliable,COUNT(*) FROM age USE INDEX(id),reliable WH
ERE age.id=reliable.id GROUP BY age_0,reliable;
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extr
a |
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
| age | index | dd_idx,id | dd_idx | 4 | NULL | 1000000 | Usin
g index; Using temporary |
| reliable | eq_ref | dd_idx,id | id | 3 | age.id | 1 |
|
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
Why does it insist on using index dd_idx for table age_0?
I even tried telling to explicitly ignore this index:
mysql> explain SELECT age_0,reliable,COUNT(*) FROM age IGNORE INDEX(dd_idx),reli
able WHERE age.id=reliable.id GROUP BY age_0,reliable;
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extr
a |
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
| age | index | dd_idx,id | dd_idx | 4 | NULL | 1000000 | Usin
g index; Using temporary |
| reliable | eq_ref | dd_idx,id | id | 3 | age.id | 1 |
|
+----------+--------+---------------+--------+---------+--------+---------+-----
-------------------------+
It still uses index(dd_idx) for table age_0 :-(
Any ideas?
regards,
thalis
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
