|
|
Posted by Christoph Burschka on 02/23/07 22:25
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
>
As already suggested, you can greatly improve the performance by normalizing the
database a bit.
In this case, let's say you have a table like the following (I don't know the
actual other fields after all):
"articles" columns:
id int(8) primary key,
title varchar(32),
tagsField text.
What you should make from this are these two tables:
"articles" columns:
id int(8) primary key,
title varchar(32).
"tags" columns:
articleId int(8),
tag varchar(32),
primary key(articleId, tag)
In this structure, each *article* would be saved only once in articles (with its
own unique number), but there would be a separate row for each tag. Then, you
can do the above query like this:
> SELECT * FROM articles JOIN tags ON id=articleId WHERE tag IN
> ('tag1','tag2','tag3') GROUP BY id;
(Excluding certain tags is a bit more complex, but is also possible.)
--
Christoph Burschka <christoph.burschka@rwth-aachen.de>
Math.-Techn. Assistent i.A.
-------------------------------------------------
RWTH Aachen
Rechen- und Kommunikationszentrum
Dienstgebäude Seffenter Weg 23
52074 Aachen
Tel: +49 (241) 80-20376
Fax: +49 (241) 80-29100
-------------------------------------------------
PGP: http://pgp.mit.edu:11371/pks/lookup?op=get&search=0xFF4BDDE8
[Back to original message]
|