Re: where match question

On 1/23/07, Jim Lucas <lists@cmsws.com> 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@alterationx.hu> 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/