CODEWITHSUNDEEP
mysqlsql
Lars Vegas
Lars Vegas

Reputation: 231

Using SQL index on left join

A topics table contains 22k topics (not that much). A topics_ignore table for users contains only 33 topics. I am using this SQL query to get the topic count for each user without the ignored topics.

SELECT 

COUNT(t.topic_id) AS num_topics

FROM topics t

LEFT JOIN topics_ignore i ON i.topic_id = t.topic_id AND i.user_id = 2

WHERE t.forum_id = 1

AND i.topic_id IS NULL

I added already two indexes on the topics table (topic_id, forum_id) and one index on topics_ignore table (topic_id)

The elapsed time is still 0.11015 seconds while other queries are in this range 0.00010s.

How can I benefit from using indexes or how to make this query faster?

Thank you

Upvotes: 0

Views: 1673

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

For this query:

SELECT COUNT(t.topic_id) AS num_topics
FROM topics t LEFT JOIN
     topics_ignore i
     ON i.topic_id = t.topic_id AND i.user_id = 2
WHERE t.forum_id = 1 AND i.topic_id IS NULL;

You want two indexes:

  • topics(forum_id, topic_id)
  • topics_ignore(topic_id, user_id)

The order of the columns in the indexes is important, so the first index is different from the index in your question.

Upvotes: 0

Mohan Reddy
Mohan Reddy

Reputation: 17

SELECT /*+RESULT_CACHE*/ 

COUNT(t.topic_id) AS num_topics   

FROM topics t

LEFT JOIN topics_ignore i ON i.topic_id = t.topic_id AND i.user_id = 2

WHERE t.forum_id = 1

AND i.topic_id IS NULL

Result Cache is a new feature in Oracle 11g and it does exactly what its name implies, it caches the results of queries and puts it into a slice of the shared pool. If you have a query that is executed often and reads data that rarely changes, this feature can increase performance significantly.

Upvotes: 0

GMB
GMB

Reputation: 222462

I would start by rewriting the query with not exists:

select count(*)
from topics t
where 
    not exists (select 1 from topics_ignore ti where ti.user_id = 2 and ti.topic_id = t.topic_id)
    and t.forum_id = 1

Then, you can create the following index, so the subquery executes quickly:

topics_ignore(user_id, topic_id)

An index on topics(forum_id, topic_id) might also help (not that the order of columns is different than in your original code).

That said, your query has a decent execution time already (0.1 sec), and your performance target (0.0001 sec) seems quite unreasonable.

Upvotes: 1

Related Questions