|
|
Posted by John Drako on 02/20/07 22:00
On Tue, 20 Feb 2007 16:12:37 -0500, Paul Lautman wrote
(in article <5416i5F1uq6qjU1@mid.individual.net>):
> John Drako wrote:
>> This is for MySQL 5.0
>>
>> I took over some php/mysql code and I'm trying to optimize it if
>> possible.
>>
>> I have one select statement on an indexed varchar field that gets
>> built and used quite frequently. The field contains a series of
>> keywords or tags. The query is supposed to find the user's selected
>> tags and sometime find the rows that exclude certain tags.
>>
>> The existing query is like so:
>>
>> SELECT * FROM tableName WHERE tagsField LIKE '%tag1%' AND tagsField
>> LIKE '%tag2%' AND tagsField NOT LIKE '%tag3%' AND NOT LIKE '%tag4%';
>>
>> the tags selected or excluded could reach as many as 30 making the
>> query long.
>>
>> Is there a better way of doing this type of query?
>>
>> TIA
>>
>> John
>
> First thing to say is that there is a better way of organising this
> type of data! You should not have lots of values in a single field.
> You should have a 1-to-many table where each tag occupies a separate
> row.
OK, understood. But, how would that enhance the search? How would you
write such a query?
The field containing these tags gets displayed on the site after
fetching the rows. So if each tag is in a row of its own, a function has
to be built to create one string out of all the rows that belong to a
certain article. Otherwise, we would need data replication to have a
field containing all the tags for display purposes and the other table
for search. And we would still end up with a long query.
I asked the question because in some programming languages you have fast
functions that can compare a string to an array of strings, so you
simply give it a main string and an array of strings and it give you
back whether any or all of the strings in the array can be found in the
main string. I was hoping that SQL has something similar that I couldn't
find on my own.
Navigation:
[Reply to this message]
|