CODEWITHSUNDEEP
sqldatabaseperformancedistinct
Algy Taylor
Algy Taylor

Reputation: 834

SELECT COUNT(DISTINCT v)) performance

Simple question. How can I rewrite a query like this:

SELECT a.name, MAX(b.value), MIN (b.value), COUNT(DISTINCT(b.value))
FROM tableA a
LEFT JOIN tableB b 
       ON a.type = b.type
WHERE b.value IS NOT NULL
GROUP BY a.name

So that it doesn't run dog-slow on a big-but-not-massive sized table? (let's say 1 million rows). Or would it be possible to do some other 'magic trick' on the database to make that query run quickly?

Normalising the data further is out of the question in this particular case :)

Bit of additional information as requested

Ideally, the solution would work for both MySQL and MS SQL Server 2008, although SQL Server is definitely the priority of those.

The two tables should look like this:

Table A:
    type INT NOT NULL PRIMARY KEY
    name VARCHAR(500

Table B:
    idTableC INT NOT NULL
    type INT NOT NULL
    value VARCHAR (50)

Table C:
    idTableC INT NOT NULL PRIMARY KEY
    ...

So generally, we want to go say: for each item in table C, get all items in table B with their type, specified in table A.

However, it is also necessary to be able to say: for every 'type' in table A, get a summary of information associated with it in table B. It's this second case that this question's concerned with :)

Upvotes: 1

Views: 1084

Answers (2)

Reza
Reza

Reputation: 19843

You can use non-clustered indexes on foreign keys, I mean 2 index on [A.Type] and [B.Type] also you can have two other indexes on the columns in select [A.Name], [B.value]

So that everything that your query needs is in an index.

Upvotes: 1

sarin
sarin

Reputation: 5307

Unsure which database you are using but you could ensure there is an index on the foreign key tableA.type and additionally an index on tableB.type which contains tableB.value. That way SQL won't need to go back to the data page to get the value and can simply retrieve it from the index. You should be careful with this as if it's a large value it could slow your index down.

Upvotes: 0

Related Questions