0

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

Improve this question
2
  • 1
    0.1 seconds seems quite fast to me. Is this really a problem? If so, see: Why should I provide an MCRE for what seems to me to be a very simple SQL query?, and note that in addition, questions about query performance always require the EXPLAIN for the given query Commented Dec 9, 2020 at 11:03
  • please show output of EXPLAIN SELECT ....rest of your query, and show the one that you mention that completes in .0001s along with EXPLAIN output for it, and show output of SHOW CREATE TABLE topics and SHOW CREATE TABLE topics_ignore Commented Dec 9, 2020 at 11:12

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you very much. the topics_ignore has already an index for the topic_id. Do I have to delete this one and add this one: CREATE INDEX forum_topic_id ON topics_ignore (user_id, topic_id);
@LarsVegas: technically you don't need to delete the old index, you can just create the new one.
Thank you and the new one goes over two columns instead of one, correct?
@LarsVegas: yes, exactly.
Hello GMB, Hello Gordon Linoff, thank you very much again for getting into my problem and thank you for the investet time. I have compared both of your solutions. At first, both of them are a big improvment. Like I said, the query has needed before you improvement 0.11015s. With the improvement from GMB the query takes only 0.01423s. Great. With the improvement from Gordon the query needs only 0.00943s. I accept the answer from gorden but GMB works also very well.
0

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.

Improve this answer

4 Comments

Thank you very much for getting into. Do you mean I need two indexes over two fields? Like this: CREATE INDEX forum_topic_id ON topics (forum_id, topic_id);
@LarsVegas . . . Yes. I fixed the typo in the answer.
Hello GMB, Hello Gordon Linoff, thank you very much again for getting into my problem and thank you for the investet time. I have compared both of your solutions. At first, both of them are a big improvment. Like I said, the query has needed before you improvement 0.11015s. With the improvement from GMB the query takes only 0.01423s. Great. With the improvement from Gordon the query needs only 0.00943s. I accept the answer from gorden but GMB works also very well.
@GordonLinoff Correct me if im wrong, your query performs better than that of GMB because GMB's query has subquery which is evaluated on every row of the topics table which is inefficient compared to your join solution provided both have optimal indexing built as described in the respective solutions.
0 
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.