Blog Tag: Mysql
Fun With MySql
1 Comment | Latest by: Phil | Add A Comment! »»
Fun with MySql! Here's the question: If you're joining two tables with a similar column, should you have an index on the first table in the query, the second table, or both? Which is faster?
I'm not a MySql DBA, but I'll take a crack at answering, and show my work. Up front, I'll say I think it's better having an index on both.
mysql> create table table_1a select fname from another_table where fname is not null limit 0,1000; Query OK, 1000 rows affected (0.08 sec) Records: 1000 Duplicates: 0 Warnings: 0
Create 4 sets of a and b tables.
SQL Foo
I was having a problem on this blog. I wanted to get the recent entries, a count of their comments, and a list of their tags. The query went something like this (this is just a sample; the real one is basically the same, but grabs more columns):
select entry_id, title, count(comment_id) as comments, group_concat(tag) as tags from entries e left join comments c on e.entry_id = c.content_id left join entries_tags et on e.entry_id = et.entry_id left join tags t on et.tag_id = t.tag_id group by e.entry_id order by e.created_at desc limit 0,2
This worked almost perfectly, except I started noticing that entries with more than one comment and more than one tag had incorrect results. Here's an example from a recent entry:
entry_id: 256
title: Royalty
comments: 20
tags: royalty,comments,royalty,comments,37s,royalty,blog,comments,37s,blog,37s,blog,37s,blog,37s,royalty,blog,comments,royalty,comments
Hmm... That doesn't look right.
select count(*) from comments where content_id = 256; +----------+ | count(*) | +----------+ | 5 | +----------+
select count(*) from entries_tags where entry_id = 256; +----------+ | count(*) | +----------+ | 4 | +----------+
OK, Five comments and four tags. "royalty,comments,37s,blog" to be exact. What's going on here? Well, the 20 comments in our result set above gives us a clue. The comments and tags are being multiplied. Duh! OK, I figured that out in like 2 seconds. But how do I fix it? I want it to say 5 comments, and only list the 4 related tags.
Enter the DISTINCT key word. I didn't know you could do this until tonight when I stumbled across it almost by accident - scouring the docs for the answer to a similar, but unrelated problem. The query now becomes:
select entry_id, title, count(distinct comment_id) as comments, group_concat(distinct tag) as tags from entries e left join comments c on e.entry_id = c.content_id left join entries_tags et on e.entry_id = et.entry_id left join tags t on et.tag_id = t.tag_id group by e.entry_id order by e.created_at desc limit 0,2
Note the placement of the distinct keywords. The result is more like what we want! Hooray!
entry_id: 256
title: Royalty
comments: 5
tags: blog,37s,royalty,comments
Hopefully, this helps other SQL noobs like me! Now if I could just get that other query to work right, we might have something...
