Hi Folks,
Have only known enough SQL to do what we needed.
Today on mysql-3.23.51 we found out that :
select something from table where index1 = 'value' or index2 = 'value';
Runs considerably slower than:
select something from table where index1 = 'value';
or
select something from table where index2 = 'value';
on a table of over 100,000 records.
changing it to :
select something from table where 'value' IN (index2,index2);
Made no difference. The speed always goes from 0.00 to 0.5x secs.
We understand from previous posts that the first index1 is found using its
index, while the second index2 has to search through the whole table.
What we can not appreciate is what can be done to bring the speed back
closer to the single index selects. It appears faster for us to do 2
seperate selects :(.
I tested mysql4 and found that with UNION the selects brought their result
back down to 0.0x secs.
>From explain:
mysql> explain select forward from user where userid='mac.man' or aliasid='mac.man';
+-------+------+------------------------+------+---------+------+--------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+------------------------+------+---------+------+--------+------------+
| user | ALL | PRIMARY,aliasid,userid | NULL | NULL | NULL | 107891 | where used |
+-------+------+------------------------+------+---------+------+--------+------------+
1 row in set (0.00 sec)
JES
--
James B. MacLean [EMAIL PROTECTED]
Department of Education
Nova Scotia, Canada
B3M 4B2
---------------------------------------------------------------------
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
