CODEWITHSUNDEEP
mysqloptimizationselect
kamikaze_pilot
kamikaze_pilot

Reputation: 14834

speed up SELECT DISTINCT using keys

If I use the SELECT DISTINCT query on a table with 100 rows where 98 entries of the table are identical and the other 2 are identical, would it still go through all 100 rows just to return the 2 distinct results?

Is there a way to use indexing/keys etc so that instead of going through all 100 rows, it would instead go through 2 rows?

####EDIT#####

so I added this index:

KEY `column` (`column`(1)),

but then when I do 

EXPLAIN SELECT DISTINCT column FROM tablename

it's still saying that it's going through all rows rather than just distinct ones

Upvotes: 2

Views: 2082

Answers (2)

Uday
Uday

Reputation: 1490

I am working on almost similar thing. I am trying to get the distinct values from a table with 400Mill rows.

I even have the key on that attribute. It is still doing the full scan. the only difference is is that it is full index scan rather than a disc scan.

i have only 10 distinct values but i didnt resulted even after 5minutes and i killed it.

Upvotes: 0

Brad Mace
Brad Mace

Reputation: 27886

Creating an index on the column or set of columns being queried with DISTINCT will speed up the query. Rather than looking through every row it will use the two entries in the index. With only 100 rows though, the difference may not even be detectable.

Upvotes: 6

Related Questions