On Sun, May 05, 2002 at 04:54:41PM +0100, 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.
Great. > 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? That one isn't fixed as easily. Since you've got multiple "starting places" for the string, you can't use a simply LIKE query. You might be able to use a BETWEEN query: http://www.mysql.com/doc/C/o/Comparison_Operators.html to help out. dancetitles.title BETWEEN "T" AND "ZZZZ" may be faster. Give it a try and see. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 87 days, processed 2,267,703,816 queries (300/sec. avg) --------------------------------------------------------------------- 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
