MySQL Database Structure vs. More Performant Query

Question

I am using MySQLi to pull blog post information from a database onto my website. My database currently has 3 tables with the following relevant columns:

blog_posts: id,
blog_post_tags: blog_post_id, tag_id,
tags: id, name

I am trying to select all blog posts by tag name and this is the query that I am using:

SELECT blog_posts.*
FROM blog_post_tags 
    LEFT JOIN (blog_posts)
    ON (blog_post_tags.blog_post_id = blog_posts.id)
WHERE blog_post_tags.tag_id
IN (
    SELECT id FROM tags
    WHERE name=$in_tag_name
)

where $in_tag_name is my PHP variable representing the name of the selected tag. However, I am not very experienced with SQL so I'm not sure if there is a more performant approach here than using a nested select.

To remove the nested select, I've considered instead of having 3 tables, just having 2 tables with the following relevant columns:

blog_posts: id,
blog_post_tags: blog_post_id, tag_name,

Then I considered the query:

SELECT blog_posts.* 
FROM blog_post_tags 
    LEFT JOIN (blog_posts) 
    ON (blog_post_tags.blog_post_id = blog_posts.id) 
WHERE blog_post_tags.tag_name=$in_tag_name

This approach gets rid of the nested query but feels less intuitive by not separating the tags table information fully from the blog posts table information.

I'm wondering if there is a best approach here of the two database structures, or else a better query than the nested select in the first database structure. I'm not sure if this is a silly question or if I'm overcomplicating things so if anyone can nudge me in the right direction here, that would be much appreciated!

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 10 '21 at 20:03
the first structure is for a blofgpost thaz have many tags and tags are shared by many posts, a normal normalized table. but still lokk for **prepares statements with parameters** before you start programming — nbk, Nov 10 '21 at 20:04

score 2 · Answer 1 · answered Nov 10 '21 at 20:05

The first schema is better, because you might want to add more information to tags, and this shouldn't be repeated for each post that uses tags. For instance, look at how Stack Exchange uses tags.

You don't need to use LEFT JOIN. Just join the three tables:

SELECT p.*
FROM blog_posts AS p
JOIN blog_post_tags AS bpt ON p.id = bpt.blog_post_id
JOIN tags AS t ON t.id = bpt.tag_id
WHERE t.name = :in_tag_name

Performance should be good if you declare all the foreign keys, which will automatically index them.

MySQL Database Structure vs. More Performant Query

1 Answers1