Re: index search

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. Andrew