Re: index search

On Fri, 2008-10-24 at 10:20 -0400, Andrew Ballard wrote: > On Thu, Oct 23, 2008 at 10:49 PM, Robert Cummings <robert@interjinn.com> wrote: > > On Thu, 2008-10-23 at 19:30 -0700, Ryan S wrote: > >> Hey all, > >> Was wondering how this is done, have a bunch of links like so: > >> 0-9 : a : b : c -----> till Z > >> > >> these will be linked to the program (so far have done this) but when the user clicks > >> any of those links I want to query the DB for just the first alphabet from the field > >> "title", using LIKE is not working for me because its catching alphabets from the middle > >> of the word as well. > > > > You're using like wrong... you want to use it with one %... > > > > where foo like 'a%' > > > >> Also how to do 0-9? do i have to have 10 if() conditions for that? > > > > Maybe you could add a new field to the table and set it to the first > > character of the field or 0 if it's a digit. Then you can index it and > > not use "like" or multiple conditions to match digits. > > > > Cheers, > > Rob. > > I'm pretty sure that if your LIKE statement begins with a constant > value as you have entered above, an index on the regular columns > should still work. :-) > > 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. -- http://www.interjinn.com Application and Templating Framework for PHP