Check if DISTINCT is really necessary

Question

I have a query in mysql as follows.

SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

Now executing the query takes approximately 13 seconds due to the size of the respective tables. However this query is a lot faster (i.e. 2 seconds)

SELECT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
ORDER BY `tableA`.`ColA` DESC `tableB`.`ColA` ASC
LIMIT 0,20

I'm aware that this may result in non-distinct rows but I'm not sure if this situation has been taken care of by the php code on the front end. So I actually want to compare the data of the two result sets both of which result in approximately 83,000 rows if not limited.

Can anyone think of a way to do this in SQL instead of doing it via a php script. I've compared the first 100 rows by eye without any issues but as we all know it only takes one exception at the 101st row to mess everything up. So I need a query that I can execute a few times every so often to see if there are rows that are in one result set that are not in the other.

Answer 1

OK, first things first, talk to the PHP devs to see if this situation is taken care of for you, which would then save a lot of pain and heartache!

Other than that, try with a group by instead of a distinct, see what the performance differences are - it may be that the group by is faster based on the indexes you have on the tables.

With the GROUP BY option, you can add a count(*) then talk to the php devs to ensure they know that if that result is 2 or more, then they need to handle it differently than if the result is a 1.

Answer 2

Have you tried replacing the order by with a group by and then having count(*) > 1 statement. That should show if there duplicated rows

Answer 3

Do a select count from each query and compare?

select count(*) from (
SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
) a

select count(*) from (
SELECT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE <whereconditions>
) a

Answer 4

You can put ORDER BY inside a separate query then use INNER JOIN to get the rest of the columns that you needed.

SELECT columns_needed FROM table1
INNER JOIN (
    SELECT DISTINCT t1.column_to_distinct, t1.primary_key, t1.colA, t2.colA
    FROM table1 t1
    LEFT JOIN table2 ON t1.colC = t2.colA
    WHERE <whereconditions>
    ORDER BY t1.colA DESC t2.a ASC
    LIMIT 0,20
) 
as sub1 ON sub1.primary_key = table1.primary_key
LEFT JOIN .....

Also put an index on (column_to_distinct,primary_key, colA) in table1 and colA in table2 as to allow MySQL to do an index scan.

Putting ORDER BY inside an INNER JOIN allows MySQL to perform sorting faster because there are fewer columns to sort.

Answer 5

Uou will need an index on tableB.colA

Also, it is more efficient to have

ORDER BY `tableA`.`ColA` DESC `tableA`.`ColC` ASC

(since tableA.ColC is the same as tableB.ColA) and an index in tableA on ColA,ColC.

The difference between your two queries shouldn't be large, so long you keep selecting a small number of articles (20 in your case) and you start close to the beginning (I assume you have pages).

Another suggestion would be to try building an index on tableB, with all columns selected in the distinct clause.

Implementing the "distinct" clause in the client is very difficult so long you work with pages (distinct records 1-20, distinct records 21-40, etc). The program will become unnecessary complicated.