>>>>> "Mike" == Mike Schwartz <mykes@stripped> writes:
Mike> I do the following:
Mike> time ./bench.pl >/dev/null
Mike> Using oracle, I get 4 seconds
Mike> Using MySQL, I get 6 minutes 22 seconds
Mike> This is running the command from the command line at least 10 times in a row -
Mike> just to be sure the system can cache as much as possible.
Mike> As near as I can tell, MySQL will use the index if the WHERE clause contains
Mike> "LIKE 'foo%'" but not if it contains "LIKE '%foo%'". In the first case, the
Mike> first 'foo' is found and then it scans the index forward until it no longer
Mike> gets a match wtih 'foo%'. In the latter case, it needs to scan from the first
Mike> values in the index through tthe entire index and select those that match.
Mike> If one of the mysql developers added the code for the latter case, we could
Mike> actually see the performance gain, if any.
Mike> Right now, all we are doing is guessing what the gain would be.
Did you declare the the key column as BINARY or test this in MySQL 3.23
If not, you are not using the MySQL index optimization in this case.
(I have mailed you a couple of times about this; Didn't you get my
mails about this ??)
Please ALWAYS post an EXPLAIN of your queries + 'mysqladmin
extended-status', as explained in the MySQL manual, if you have any
questions about how MySQL optimizes things.