StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL - three-table tag structure - efficiently getting tag names and counts from query
text
Body
copied!<p>I have a pretty standard 3-table tag schema where I have a table named <code>content</code> with fields such as <code>title</code>, <code>subtitle</code>, <code>text</code>, etc., a table named <code>tag</code> with fields for <code>id</code> and <code>name</code>, and a table called <code>content_tag</code> that joins the two with fields <code>content_id</code> and <code>tag_id</code>. </p> <p>I allow the user to search by both tags and keywords. The queries for this look like this:</p> <pre><code>SELECT * FROM content_tag ct, content c, tag t WHERE ct.tag_id = t.id AND (t.name IN ('Metal Joints','Joint Set')) AND c.id = ct.content_id AND (c.title like '%Te%' or c.subtitle like '%Te%' or c.text like '%Te%' or c.keywords like '%Te%' ) GROUP BY c.id HAVING COUNT( c.id )=2 ORDER BY add_date DESC </code></pre> <p>This query selects all content that have both the tags '<code>Metal Joints</code>' and '<code>Joint Set</code>' that also contains '<code>%Te</code>'. It works well and is not my issue.</p> <p>My issue is trying to get all the tag names along with their count from this query efficiently. I tried this with subquery <code>WHERE IN</code> statements, but they were taking anywhere from 3-5 seconds to complete.</p> <p>I need help creating a query that will give me a list of tag names and counts contained in the table the above query generates. I can't seem to do it without it taking too long.</p> <p>EDIT: Here's the query I'm using now that takes a few seconds, but works.</p> <pre><code>SELECT tag.name, COUNT(tag.id) as tagCount FROM content_tag LEFT JOIN tag ON content_tag.tag_id = tag.id WHERE content_tag.content_id IN (SELECT c.id FROM content_tag ct, content c, tag t WHERE ct.tag_id = t.id AND (t.name IN ('Metal Joints','Joint Set')) AND c.id = ct.content_id AND (c.title like '%Te%' or c.subtitle like '%Te%' or c.text like '%Te%' or c.keywords like '%Te%' ) GROUP BY c.id HAVING COUNT( c.id )=2 ORDER BY add_date DESC) GROUP BY tag.id </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload