Geek asks and answers

Is it possible to make this SQL query faster?

Curious if this query can be made faster? or if there are other similar queries that will work better?

SELECT id,source FROM posts
WHERE id = ANY(SELECT image_id FROM `post_tags` WHERE tag_id = (SELECT id FROM `tags` WHERE tag = _utf8 '$TAG' collate utf8_bin))
  AND posts.exists = 'n'
ORDER BY posts.ratecount DESC
LIMIT 0,100

Without use:

  AND posts.exists = 'n'
ORDER BY posts.ratecount
DESC LIMIT 0,100

It speeds up for queries to useful levels, but needs it to some extent for what I'm doing.

  • The tag table has a unique index for both the "tag" and the "id".
  • Tags have 83K lines.
  • Post_tags has a unique index for 'image_id', 'tag_id'. Also a normal index for everyone.
  • Post_tags has 471K lines.
  • Messages have a unique index for 'id'. Also the normal index for "exists" and "ratecount".
  • The message table has about 1.1M lines.
+3
source share
2 answers

, JOIN, - .

SELECT * FROM posts
LEFT JOIN post_tags ON post_tags.image_id = posts.id
JOIN tags ON post_tags.tag_id = tags.id
WHERE tags.tag = _utf8 '$tag' collate utf8_bin
  AND posts.exists = 'n'
ORDER BY posts.ratecount DESC
LIMIT 0,100

, 22 s > 0,26 .

0

TAG? ID TAG? TAG(TAG, ID), .

POST_TAGS? TAG_ID IMAGE_ID? , POST_TAGS(IMAGE_ID, TAG_ID).

, , POST_TAGS(TAG_ID, IMAGE_ID) POST_TAGS(IMAGE_ID, TAG_ID), .

POSTS, POSTS(ID, POSTS, RATEACCOUNT), ( , INSERT, UPDATE DELETE, ).

, JOIN , .

0

More articles:


All Articles