[ubuntu-florida] MySQL and REGEX

j.e.aneiros jesus.aneiros at gmail.com
Tue Sep 21 20:58:52 BST 2010


I think is the way the query optimizer works in MySQL, remember that it is
gonna try to eliminate all the rows it can, so it you help the optimizer a
little by putting the more restrictive tests before the other it is gonna
say "thank you". That's what I recalled from DuBois book, if my memory is
OK.

On Tue, Sep 21, 2010 at 3:17 PM, Ryan Parrish <ryan at stickystyle.net> wrote:

> On Tue, Sep 21, 2010 at 1:27 PM, j.e.aneiros <jesus.aneiros at gmail.com>
> wrote:
> > Thanks for the clever observation Ryan. Perhaps to "help" the optimizer a
> > little maybe the REGEXP could be combined with a LIKE part in the same
> > query?
> >
>
> Yes, adding a LIKE operator to the query with a regex will help with
> the performance of the query greatly if it can be applied in your
> situation.  The optimizer will evaluate the LIKE pattern first (which
> mysql is actually pretty fast at) and then apply the regex to the
> remaining result set.
>
> Although the above is based on my observations doing an EXPLAIN on the
> queries, I can't seem to find concreate docs that say this is
> precisely how the optimizer handles LIKE + REGEX queries, I just know
> it's not looking at all 1M rows when done this way on my db.
>
> --Ryan
>
> --
> Ubuntu-us-fl mailing list
> Ubuntu-us-fl at lists.ubuntu.com
> Modify settings or unsubscribe at:
> https://lists.ubuntu.com/mailman/listinfo/ubuntu-us-fl
>



-- 
J. E. Aneiros
GNU/Linux User #190716 en http://counter.li.org
perl -e '$_=pack(c5,0105,0107,0123,0132,(1<<3)+2);y[A-Z][N-ZA-M];print;'
PK fingerprint: 5179 917E 5B34 F073 E11A  AFB3 4CB3 5301 4A80 F674
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://lists.ubuntu.com/archives/ubuntu-us-fl/attachments/20100921/ba95e810/attachment.htm 


More information about the Ubuntu-us-fl mailing list