Re: [PHP] index search
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
Re: [PHP] 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 [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
Re: [PHP] index search
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
Re: [PHP] 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 [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
Re: [PHP] index search
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
Re: [PHP] index search
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
[PHP] index search
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
Re: [PHP] index search
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
