Re: index search

On Fri, Oct 24, 2008 at 11:08 AM, Robert Cummings <robert@interjinn.com> wrote: > 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. Agreed. > 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. Agreed. > 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. Also agreed. See? I can be rather agreeable. :-) I'm not saying that what you proposed is a bad solution. I'll even grant that in some cases it can be an excellent solution. I just don't think it's necessary in THIS case. Andrew