MYSQL search query optimization from two many-to-many tables

user_9320316 0
Viewed 42

I have three tables.

  1. tbl_post for a table of posts. (post_idx, post_created, post_title, ...)
  2. tbl_mention for a table of mentions. (mention_idx, mention_name, mention_img, ...)
  3. tbl_post_mention for a unique many-to-many relation between the two tables. (post_idx, mention_idx)

For example,

  • PostA can have MentionA and MentionB.
  • PostB can have MentionA and MentionC.
  • PostC cannot have MentionC and MentionC.

tbl_post has about million rows, tbl_mention has less than hundred rows, and tbl_post_mention has a couple of million rows. All three tables are heavily loaded with foreign keys, unique indices, etc.

I am trying to make two separate search queries.

  1. Search for post ids with all the given mention ids[AND condition]
  2. Search for post ids with any of the given mention ids[OR condition]

Then join with tbl_post and tbl_mention to populate with meaningful data, order the results, and return the top n. In the end, I hope to have a n list of posts with all the data required for my service to display on the front end.

Here are the respective simpler queries

SELECT post_idx 
FROM 
    (SELECT post_idx, count(*) as c 
    FROM tbl_post_mention 
    WHERE mention_idx in (1,95) 
    GROUP BY post_idx) AS A
WHERE c >= 2;

The problem with this query is that it is already inefficient before the joins and ordering. This process alone takes 0.2 seconds.

SELECT DISTINCT post_idx
FROM tbl_post_mention 
WHERE mention_idx in (1,95);

This is a simple index range scan, but because of the IN statement, the query becomes expensive again once you start joining it with other tables.

I tried more complex and "clever" queries and tried indexing different sets of columns with no avail. Are there special syntaxes that I could use in this case? Maybe a clever trick? Partitioning? Or am I missing some fundamental concept here... :(

Send help.

query-optimization mysql
1 Answers

The query you want is this:

SELECT post_idx 
  FROM tbl_post_mention 
 WHERE mention_idx in (1,95) 
 GROUP BY post_idx
HAVING COUNT(*) >= 2

The HAVING clause does your post-GROUP BY filtering.

The index that will help you is this.

CREATE INDEX mentionsdex ON tbl_post_mention (mention_idx, post_idx);

It covers your query by allowing rapid lookup by mention_idx then grouping by post_idx.

Often so-called join tables with two columns -- like your tbl_post_mention -- work most efficiently when they have a pair of indexes with the columns in opposite orders.

user_2056080
Related