Re: Trouble with indexing
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 = MySQL can use index on Column to find the match. When you match: ... function(Column) = . 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
Re: Trouble with indexing
FilterFodder: sql, query Yo Jeremy, On Sunday 05 May 2002 9:31 pm, Jeremy Zawodny wrote: > That one isn't fixed as easily. ... > You might be able to use a BETWEEN query: (eyes raised to heaven, hands clasped in prayer) o jeremy you are my hero! (grin) seriously, thanks a lot, i really appreciate your efforts on my behalf - all yr suggestions worked a treat. stay well, stay cool Donna - 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
Re: Trouble with indexing
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 "" 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
Re: Trouble with indexing
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. 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? Donna - 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
Re: Trouble with indexing
On Sun, May 05, 2002 at 03:21:59AM +0100, Donna Robinson wrote: > > explain select dances.danceid, dancetitles.title from dances, > dancetitles where dances.danceid=dancetitles.danceid and > dancetitles.title regexp "^A" and dances.has_desc='T' order by > title; [snip] > This is TERRIBLE! But I can't figure out how to improve it. Can > anyone help? 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. 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 86 days, processed 2,253,903,006 queries (301/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
