CODEWITHSUNDEEP
sqlperformancequery-optimization
user1270123
user1270123

Reputation: 573

OPtimizing the SQL Query

I want to optimize the following query 

Select Distinct(Column_A) from Table_name where Column_B = 'Something'

THis basically does a full table scan (around 7.5 million records). I would like to know if I can add an index on this column_A so that it would do a FULL index scan, Or is there any other better solution for this?

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can create an index on Table_name(Column_B, Column_A). The index "covers" the query, so only the index should be accessed for the query.

EDIT:

I keep thinking about this. Even with the index, some SQL engines are likely to sort the data for resolving the distinct. The following query might possibly be faster:

select column_A
from table_name t
where column_B = 'Something' and
      t.id = (select min(id)
              from table_name t2
              where t2.column_A = t.column_A and t2.column_B = t.column_B
             );

For this to work, it would need an index on table_name(column_A, column_B, id). This assumes a unique id on each row in addition to the index.

Upvotes: 4

Related Questions