Why is a 'Select distinct limit 100' using tmp tables? 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.
Query
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.
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,

SELECT GROUP_CONCAT(DISTINCT col1),
       GROUP_CONCAT(DISTINCT col2),
       ...
    FROM TABLE;


(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).