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.