Welcome! Log In Create A New Profile

Advanced

[PHP] index search

Posted by Ryan S 
Ryan S
[PHP] index search
October 24, 2008 04:35AM
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.

Also how to do 0-9? do i have to have 10 if() conditions for that?

Ideas and suggestions welcome.

Thanks!
Ryan

------
- The faulty interface lies between the chair and the keyboard.
- Creativity is great, but plagiarism is faster!
- Smile, everyone loves a moron. :-)





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Robert Cummings
Re: [PHP] index search
October 24, 2008 04:50AM
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.
--
http://www.interjinn.com
Application and Templating Framework for PHP


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Andrew Ballard
Re: [PHP] index search
October 24, 2008 04:25PM
On Thu, Oct 23, 2008 at 10:49 PM, Robert Cummings <[email protected]> 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.)

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Robert Cummings
Re: [PHP] index search
October 24, 2008 04:25PM
On Fri, 2008-10-24 at 10:20 -0400, Andrew Ballard wrote:
> On Thu, Oct 23, 2008 at 10:49 PM, Robert Cummings <[email protected]> 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


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Andrew Ballard
Re: [PHP] index search
October 24, 2008 04:55PM
On Fri, Oct 24, 2008 at 10:22 AM, Robert Cummings <[email protected]> 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

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Robert Cummings
Re: [PHP] index search
October 24, 2008 05:10PM
On Fri, 2008-10-24 at 10:49 -0400, Andrew Ballard wrote:
> On Fri, Oct 24, 2008 at 10:22 AM, Robert Cummings <[email protected]> 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


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Andrew Ballard
Re: [PHP] index search
October 24, 2008 05:35PM
On Fri, Oct 24, 2008 at 11:08 AM, Robert Cummings <[email protected]> wrote:
> On Fri, 2008-10-24 at 10:49 -0400, Andrew Ballard wrote:
>> On Fri, Oct 24, 2008 at 10:22 AM, Robert Cummings <[email protected]> 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

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Ryan S
Re: [PHP] index search
October 24, 2008 06:10PM
<clip>
Are you sure LIKE isn't working? I would think LIKE "a%" would work. For
0-9, I would think you can use <= 9 or you can use BETWEEN 0 and 9.
</clip>

Silly me, the reason it was not working was I am so used to %something% and used that instead of
something%

Anyway, it was a not a useless post for me because the 0-9 part was not solved in my head but a lot of interesting replies as to how to do it.

Thanks everyone!

/Ryan





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Sorry, only registered users may post in this forum.

Click here to login