2
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

Since lm_blog_tags.tag is UNIQUE, I don't quite see why you need tag_id. If you get rid of it, you can use lm_blog_tags.tag directly in lm_blog_tag_relationships.

Untested and assuming the table are redesigned according to the above paragraph:

SELECT T2.blog_id, T2.title, T2.url, T2.summary, T2.article, GROUP_CONCAT(T4.tag SEPARATOR ':')
FROM lm_blog AS T2
INNER JOIN lm_blog_tag_relationships as T4 on T2.blog_id = T4.blog_id
WHERE T2.blog_id IN (SELECT blog_id FROM lm_blog_tag_relationships WHERE tag == 'xxxxx')
GROUP BY T2.blog_id
ORDER BY T2.blog_id DESC

You could also use a sub-query and IN even if you do not redesign the tables.

I did not quite get your line:

HAVING tags   LIKE '%:PHP:%' OR tags LIKE '%:PHP' OR tags LIKE 'PHP:%'

I guess tags is lm_blog_tags.tag. And I would expect each such tag to be just one word, not concatenated words with a : separator as you output. I assume you have the join table because there is only one word per lm_blog_tags.tag.

\$\endgroup\$
2
  • \$\begingroup\$ concat will add the : betwwen tags so if php was the first tag it would look like php: and if it was the last it would be :php. My terrible like clause was to find those and if they are in the mddle :php: I recall reading that joining on numeric ids is faster than a unique string. Is it true or should I drop the numeric ids? \$\endgroup\$
    – yamikoWebs
    Aug 28 '13 at 20:03
  • \$\begingroup\$ I don't know about the performance issue... I had not thought about that. But it adds a lot of complexity, so you should weigh that carefully. \$\endgroup\$
    – toto2
    Aug 28 '13 at 21:20
0
\$\begingroup\$

SQL databases are supposed to be good at joins and indexed searches, not so much unindexed string pattern matching. Therefore, I would replace your HAVING tags LIKE … clause with a WHERE EXISTS subselect.

I also renamed your table aliases to make more sense.

SELECT  b.blog_id, b.title, b.url, b.summary, b.article,
        GROUP_CONCAT(t.tag SEPARATOR ':') AS tags
    FROM
        lm_blog AS b
            INNER JOIN lm_blog_tag_relationships AS bt
                ON bt.blog_id = b.blog_id
            INNER JOIN lm_blog_tags AS t
                ON t.tag_id = bt.tag_id
    WHERE EXISTS (
        SELECT filter_t.tag_id
            FROM
                lm_blog_tags AS filter_bt
                    INNER JOIN lm_tags AS filter_t
                        ON filter_t.tag_id = filter_bt.tag_id
            WHERE
                filter_bt.blog_id = b.blog_id AND
                filter_t.tag = 'PHP'
    )
    GROUP BY b.blog_id
    ORDER BY b.blog_id DESC
\$\endgroup\$
3
  • \$\begingroup\$ I thought sub selects are also inefficient and should be used only when absolutely needed. \$\endgroup\$
    – yamikoWebs
    Aug 28 '13 at 19:59
  • \$\begingroup\$ The only way to tell which approach runs faster in practice on MySQL is to try it with real data. However, unless you have a demonstrated performance problem, you are probably better off saying what you mean in SQL rather than using string-matching hacks. \$\endgroup\$ Aug 28 '13 at 20:06
  • \$\begingroup\$ My string hack is terrible which is why I posted the question :) \$\endgroup\$
    – yamikoWebs
    Aug 29 '13 at 1:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.