Hi, I have a problem where the index for a SET column does not get used
once I link in another table.
The output to explain:
mysql> explain select count(*) from NS_articles where
find_in_set('approved', artFlags) > 0;
+-------------+-------+---------------+----------+---------+------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-------------+-------+---------------+----------+---------+------+------+-------------------------+
| NS_articles | index | NULL | artFlags | 2 | NULL | 560
| where used; Using index |
+-------------+-------+---------------+----------+---------+------+------+-------------------------+
This is good, the index on artFlags is being used. Now I link in another
table:
mysql> explain select count(*) searchTotal from NS_articles nsa,
NS_editors nse where find_in_set('approved', nsa.artFlags) > 0 and
nsa.artEditor = nse.id;
+-------+-------+---------------+-----------+---------+--------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+-----------+---------+--------+------+-------------+
| nse | index | PRIMARY | PRIMARY | 32 | NULL | 21 |
Using index |
| nsa | ref | artEditor | artEditor | 32 | nse.id | 19 |
where used |
+-------+-------+---------------+-----------+---------+--------+------+-------------+
What happened to the index on artFlags? The table link uses indexes
which is good, but now artFlags is doing a raw table scan.
TIA
Monte
---------------------------------------------------------------------
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
