Hi Donna, Donna Robinson wrote: >Hi Jeremy, > >On Sunday 05 May 2002 5:52 am, Jeremy Zawodny wrote: > >>REGEXP queries don't use indexes. Change the: >> REGEXP "^A" >>to >> LIKE "A%" >>and it'll use the index and give you a nice speed boost. >> > >which indeed it did! I checked the manual (yet again) and it doesn't actually >say anything about regexp not using indices (grr). So I went thru the db fns >and stripped out regexp and replaced with like. > It is true for every where clause which uses function. Think about it. When you match: ... Column = <expr> MySQL can use index on Column to find the match. When you match: ... function(Column) = <expr>. MySQL must evaluate result of function and just then compare this result. How can index be used in this case? LIKE is not a function but comparison operator so it can be optimized in case it matches Prefix of column. This is because of nature of keys in MySQL they can be used for prefix-match. Column LIKE "%A" - will not use index too.
> >BUT I have hit a snag on the last query to fix: >select dances.danceid as id, dancetitles.title from dances, dancetitles where >dances.danceid=dancetitles.danceid and dances.has_crib='T' and >dancetitles.title regexp "^[T-Z]"; > >Despite multiple offerings of every perm and comb I can think of (and even >rtfm) I can't persuade mysql to eat a "like" query which will return the same >ans as the regexp one. Am beginning to think am flogging a dead horse here? > If you often need to match agains first letter it will be wize to use prefix index on first letter only. ALTER TABLE dancetitles ADD KEY (title(1)); This will be very small and fast index. I want to mention also that in your first posting table dancetitles has no index at all on title column. > >Donna > -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria <___/ www.mysql.com M: +359 88 231668 --------------------------------------------------------------------- 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
