Re: index search

On Fri, 2008-10-24 at 10:49 -0400, Andrew Ballard wrote: > On Fri, Oct 24, 2008 at 10:22 AM, Robert Cummings <robert@interjinn.com> wrote: > >> As for the numeric comparison, I know MySQL has a REGEXP comparison > >> similar to the LIKE comparator, but I don't know enough about MySQL to > >> know if it can similarly benefit from indexes the same way. (SQL > >> Server will let you say WHERE foo LIKE '[0-9]%', but this doesn't seem > >> to work in MySQL.) > > > > Yeah, I know about MySQL's regexp, but that didn't seem terribly efficient. For > > small databases, under a million records, I prefer to trade space for time. > > > > Cheers, > > Rob. > > I've never used the regexp in MySQL, so I have no idea how it impacts > performance; I just saw it in the manual (where the comments confirm > that REGEXP does not use indexes - yuk). > > In some cases, I agree with you that a small tradeoff in space to save > time is worthwhile. In this case though, I think it would work to say > WHERE foo BETWEEN '0' AND '9' and WHERE foo LIKE 'a%' since both > are > able to use an existing index and don't need to maintain an additional > column. Maintaining that extra column is like a one liner in the insert code. Also the width of the field at one byte is pretty teency... a few more bytes if you use UTF and plan to index non ascii characters. Since this is a library of wine, it's more likely the case of occasional insert and many many reads. So I think this is an exemplary candidate for a separate indexed field. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP