Improve my SQL select statement

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

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.

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