I hope someone can teach me why MySQL is using temporary tables on a super simple select with a limit of 100 and how to get it do query more efficiently. I'm running MySQL 5.1.53-community.

The following query should check row by row, save it to memory if it's unique from the other's already selected or skip if it is a repeat. Once results 100 are in memory it should terminate the query and output the result. Instead it goes through every single result, loads them into a temporary table on the hard disk sorts them and pulls the first hundred sorted ascending. It should take a fraction of a second, not several hours.


SELECT DISTINCT `some field` FROM `myTable` LIMIT 100
EXPLAIN SELECT DISTINCT `some field` FROM `myTable` LIMIT 100:
id|select_type|table   |type|possible_keys|key|key_len|ref|rows      |Extra
1 |SIMPLE     |myTable | ALL|             |   |       |   |223193820 |Using temporary

There's no reason for it to sort anything aside from that's how it's programmed. It only needs to return 100 distinct values which I could do much faster manually parsing the raw text.

If it only needs 100 distinct values it doesn't have to check 220 million rows to make sure that a value isn't duplicated in the 100 rows that it is supposed to return.

  • 2
    I have moved discussion on this question to chat. Use that facility for further discussion (if necessary). – Paul White 9 Nov 28 '16 at 5:05

There are multiple ways that SELECT DISTINCT col FROM ... LIMIT 100 can be performed:

Case 1: Create a temp table with all rows of col, sort it, then peel off the first 100 (de-dupping as it goes).

Case 2: Build a hash in memory, read col putting it into the hash, stop after 100. (Fast.)

Case 3 (if there is INDEX(col)): Leapfrog through the index. This effectively takes 100 BTree dives into the index. (Fast, even if few distinct values.)

Which will it do? It depends on the version, the data, the existence of an index, etc. Note that 5.1 is antique; you should plan to upgrade to 5.5, then 5.6, then 5.7. (8.0 is also available, but not "GA".) The Optimizer has changed a couple of times in this area, so I cannot say what your version will do.

If you are going to do this for all columns, consider, instead,


(No LIMIT.) This won't give you exactly 100 of each, and it will probably give you only the alphabetically/numerically first several (1024 bytes' worth) of each list, but maybe that is good enough. It is probably faster than column-by-column.

See also ANALYSE() (spelled that way).

| improve this answer | |

The answer is that mysql 5.1 performs DISTINCT as group by and order by actions before truncating the rest of the results. It's designers chose to make it write the full data set of the column to a temp table and then sort them even though you probably don't need it to.

In my opinion it makes DISTINCT fairly worthless because you can just as easily use group by.

| improve this answer | |
  • Not sure what you mean with "makes DISTINCT fairly worthless". SELECT DISTINCT <columns> FROM tbl; is equivalent to SELECT <columns> FROM tbl GROUP BY <columns> ; in standard SQL and all SQL products. The implementation details are irrelevant. – ypercubeᵀᴹ Nov 28 '16 at 15:53
  • Irrelevant? If a user only needs 100 distinct results there is no reason to go through the whole list of potentially millions of distinct values. What's the point of the redundancy? – Aunt Jemima Nov 28 '16 at 17:16
  • My point is that if DISTINCT is worthless, so is GROUP BY. Your answer suggests that they worth differently ;) – ypercubeᵀᴹ Nov 28 '16 at 17:18
  • Distinct is worthless because it is a redundancy of group by. Is that really the reason you marked it down? – Aunt Jemima Nov 28 '16 at 17:20
  • 1
    I suggest that DISTINCT and GROUP BY have different semantics, so both should be kept in the language. The former refers to "getting a list of unique values"; the latter begs for aggregation (sum, count, etc). It is almost coincidence that some DISTINCTs can be implemented via GROUP BY. The Optimizer will do whatever is 'best' in either case. – Rick James Nov 28 '16 at 19:17

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