I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good resources for learning more about query optimization!


FROM messages m 
INNER JOIN message_entries t2
    ON m.id = t2.message_id
WHERE m.user_id = 1234 
    AND m.type = 1 
ORDER BY m.sent_on DESC;

The messages table has a covering index for (user_id, type, sent_on, created) and it uses this index for the query, and the message_entries table has an index on message_id (through foreign key).

I've tried adding id to the covering index on the messages table (at the beginning or end of the index, i've tried both), but it still chooses to use the original index for user_id, type, sent_on, created.

What can I do to improve performance when adding joins to the base query?

  • 1
    Care to post the actual plan? – mustaccio Apr 9 '19 at 16:48
  • The JOIN you added provides nothing extra -- Let's see a useful JOIN. There are many possible answers to your general question, depending on the details of the JOIN ! – Rick James Apr 22 '19 at 18:14

Step 1. Get rid of the JOIN.

The main purpose of t2 (the way the query is written) is to see if there are amy message_entries for the given message. Do you really need that test? If not, get rid of the JOIN

Step 2. Get rid of the DISTINCT.

The secondary purpose of the JOIN (should you decide to keep it) is to create multiple rows, thereby necessitating the DISTINCT. But, since it is only testing for the existence of at least one 'entry', toss the JOIN and add this to the WHERE:

AND EXISTS ( SELECT 1 FROM message_entries WHERE m.id = message_id )

Step 3: Better INDEX (Ok, you already have a good index)

INDEX(user_id, type, sent_on)

This may allow for getting through the WHERE and on to the ORDER BY. Note: The ORDER BY caused the temp and filesort.

Note: A temp and/or filesort is not evil in itself; it is just a sign of either a complex query (where it is necessary) or a missing index or an inefficiently formulated query.

Huh? Since you don't have a LIMIT, why do you have SQL_CALC_FOUND_ROWS? Count the number of rows in the resultset to get the "found rows".

| improve this answer | |
  • All of this is really good information! I do have a LIMIT in certain situations, I just simplified the resulting query too much. The biggest issue here is that I'm building out potentially complex dynamic queries, but it's good to realize that in certain situations I can simplify even more. Does the SQL_CALC_FOUND_ROWS add a lot of overhead? I could somewhat easily count the rows in PHP after retrieval if it's not limited, but honestly most of my load comes from cases where pagination/limiting is necessary. Thank you for the information! – robert.bo.roth Apr 23 '19 at 19:32
  • @robert.bo.roth - Simplifying the query leads to irrelevant Answers. EAV: mysql.rjweb.org/doc.php/eav and Pagination: mysql.rjweb.org/doc.php/pagination In the long run, "using temporary" will be the least of your problems. – Rick James Apr 23 '19 at 22:24
  • Unfortunately due to the nature of the projects I'm working on (NDAs, etc) I don't feel comfortable sharing the full extent of my queries :( Also, to give you some idea of how complicated it gets: this query is generated dynamically from a stored procedure that accepts somewhere around 25-30 parameters that could all affect the final query. I'll basically need to just keep creating separate questions as I discover certain cases that have optimization issues, but the number of possible queries that can be generated from this stored procedure is quite large. – robert.bo.roth Apr 25 '19 at 15:55
  • Generated queries and 26-30 params -- I lean toward having a few params as indexed columns; the rest buried in JSON to be tested after the 'few' have narrowed down the search. I briefly mention this approach in my 'eav' doc. – Rick James Apr 25 '19 at 21:39

There is no chance to remove Using temporary; Using filesort on that query.

If m.id is unique (or even primary key), DISTINCT is excessive. If not, the ORDER BY is random.- Akina

| improve this answer | |
  • I can remove DISTINCT for this simple query (it's being built dynamically, so it needs DISTINCT for other queries that have LEFT JOIN, but I can do some sort of check to prevent that). The order by is random, but I thought that would be the point of having an index on the sent_on column, so that it would be pre-sorted and thus not need to be sorted during the query? – robert.bo.roth Apr 13 '19 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.