Knowledgebase
MySQL : select tags where tag=x
| Posted by kamurj, 01-09-2013, 12:01 PM | | I have tables media, tags, tag_content
c_ media
mid (primary)
m_title (varchar)
m_date (int 11, unix timestamp) index
tags
t_id (primary)
t_name (varchar)
t_type (varchar)
key: tag (columns t_name, t_type)
tag_content
tc_id (int 11) (same as t_id from tags table)
tc_cid (int 11) (same as mid from c_media table)
key: tc (columns tc_id, tc_cid)
the goal is to get top tags from stories which also have a common tag (in this case 162) and were created within the last 24 hours. This seems to work but is slow, thanks for your help.
This is the result of explain http://www.efabula.com/i/tmp-sql-explain.jpg
|
| Posted by MattE, 01-09-2013, 03:57 PM | | Assuming a large c_media table, maybe setup a separate table that includes only the 'mid' from the past 24 hours (refreshed on insert to c_media or via cronjob), and reference that as the first INNER JOIN. I can't think of another way to speed up what you have provided, your indexes & primaries appear in order, it is just a large number of rows to sort through at the top of the query.
I am curious to know if swapping `t1.tc_cid = mid AND t1.tc_id = '162'` around would provide better results given the amount of rows where `t1.tc_cid = mid` would evaluate to true versus `t1.tc_id = '162'`. Whether this makes a difference at all to MySQL is unknown to me, but worth testing unless someone comes along that does know the answer.
|
| Posted by kamurj, 01-10-2013, 09:04 AM | | thank you MattE, I changed the places of `t1.tc_cid = mid` would evaluate to true versus `t1.tc_id = '162'` and it does seem to faster.
|
|
 Add to Favourites
 Print this Article
|
Also Read
Spam Killers (Views: 563)
Load (Views: 608)
