Blog & Blog Tags SQL Statements I have three tables for a blog system.

The blog

CREATE TABLE IF NOT EXISTS lm_blog(
        blog_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        title VARCHAR(80) NOT NULL UNIQUE,
        action VARCHAR(80) NOT NULL UNIQUE INDEX,
        url VARCHAR(80) NOT NULL UNIQUE,
        summary VARCHAR (255) NOT NULL,
        article TEXT NOT NULL,
        created DATE NOT NULL,
        updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        publish TINYINT UNSIGNED DEFAULT 0,
        PRIMARY KEY(blog_id));


The tags

CREATE TABLE IF NOT EXISTS lm_blog_tags(
        tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        tag VARCHAR(32) NOT NULL UNIQUE INDEX,
        description TEXT,
        PRIMARY KEY(tag_id)
    );


And the relationships

CREATE TABLE IF NOT EXISTS lm_blog_tag_relationships(
        blog_id INT UNSIGNED NOT NULL,
        tag_id INT UNSIGNED NOT NULL,
        PRIMARY KEY(blog_id, tag_id),
        FOREIGN KEY (blog_id) REFERENCES lm_blog(blog_id),
        FOREIGN KEY (tag_id) REFERENCES lm_blog_tags(tag_id)
    );


I want to search for blogs by a tag name and have all the tags for that blog. I am currently using GROUP_CONCAT to group the tags and filter it with a HAVING LIKE clause.

I.E to search for aticles with a PHP tag

SELECT T2.blog_id, T2.title, T2.url, T2.summary, T2.article, GROUP_CONCAT(T3.tag SEPARATOR ':') AS tags
FROM  lm_blog_tag_relationships AS T1
INNER JOIN lm_blog AS T2 ON T1.blog_id = T2.blog_id
INNER JOIN lm_blog_tags AS T3 ON T1.tag_id = T3.tag_id
GROUP BY T2.blog_id
HAVING tags   LIKE '%:PHP:%' OR tags LIKE '%:PHP' OR tags LIKE 'PHP:%'
ORDER BY T2.blog_id DESC


I'm assuming there is a better way to get the same results.
Using a like with a leading wild card will likely be very slow as no indexes can be used. As such they should be avoided, which should be easy to do when the string you are checking it a concatenation of values from a table.

I would suggest you do another join from the blog table to the blog tag relationship table, and from that another join to the blog tag table but specifying that the tag is php . This should mean any blog with a php tag will be brought back (complete with all its tags concatenated together) while any blog without that tag will be ignored.

Not tested, but something like the following

SELECT T2.blog_id, T2.title, T2.url, T2.summary, T2.article, GROUP_CONCAT(T3.tag SEPARATOR ':') AS tags
FROM  lm_blog_tag_relationships AS T1
INNER JOIN lm_blog AS T2 ON T1.blog_id = T2.blog_id
INNER JOIN lm_blog_tags AS T3 ON T1.tag_id = T3.tag_id
INNER JOIN lm_blog_tag_relationships T4 ON T2.blog_id = T4.blog_id
INNER JOIN lm_blog_tags AS T5 ON T4.tag_id = T5.tag_id AND T5.tag SEPARATOR = 'PHP'
GROUP BY T2.blog_id
ORDER BY T2.blog_id DESC