On 1/23/07, Jim Lucas <lists@xxxxxxxxx> wrote:
Németh Zoltán wrote:
> On k, 2007-01-23 at 19:46 +1100, chris smith wrote:
>> On 1/23/07, Németh Zoltán <znemeth@xxxxxxxxxxxxxx> wrote:
>>> On h, 2007-01-22 at 22:53 -0800, Jim Lucas wrote:
>>>> Don wrote:
>>>>> I have a db field that contains zip codes separated by comas.
>>>>>
>>>>> I am trying to get php to return all of the rows that contain a particular
>>>>> zip code.
>>>>>
>>>>>
>>>>>
>>>>> $query = "SELECT * FROM info WHERE MATCH (partialZIP) AGAINST ('$zip')";
>>>> try this
>>>>
>>>> $query = "SELECT * FROM info WHERE column LIKE '{$zip}'";
>>> I would use
>>>
>>> $query = "SELECT * FROM info WHERE LOCATE('{$zip}', column) > 0";
>> And how are you going to index that? That's going to be extremely slow
>> as the size of the table grows.
>>
>
> well, yes.
>
> better solution is to not store the zip codes in one field with commas,
> but in a separate table which relates to this one. and then you could
> use a query like
>
> $query = "SELECT t1.*, t2.* FROM info t1, zips t2 WHERE t1.id=t2.infoid
> AND t2.zip = '{$zip}'";
>
> greets
> Zoltán Németh
>
But, since the op is working with existing data, what is the performance
difference between using LIKE or LOCATE?
Pro's vs. Con's
This is getting pretty OT but that's my fault... so I'll post this and
if anyone has any questions send them off-list ;)
This stuff isn't mysql specific, it should be the same across all types of db's.
This:
LIKE '{$zip}'
will be able to use an index on the field because there are no
wildcards involved, so it's much the same as field='{$zip}'. I have no
idea if databases can make this conversion internally, but in theory
it should be the same.
If it's like this:
like '{$zip}%'
that means $zip is at the start of the field and you could use a
partial index (eg on the first 10 characters of the field, search
relevant database docs for how to do that).
If it's like this:
like '%{$zip}'
then $zip is at the end of the field and you'd probably benefit from
creating an extra database field, reversing the last "X" characters,
creating an index and using that for searching (and create a database
trigger to keep it up to date).
If it's like this:
like '%{$zip}%'
there's not much of a chance for that to use an index because the
string can be anywhere in the field.
LOCATE will be much the same as this because it has to find the string
somewhere in the whole field, then return it's position. Actually it
will probably be a lot worse because of that.
Hopefully that makes things as clear as mud! ;)
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
