5

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.

  • 2
    The crucial missing information is what the unique/primary/foreign keys are. If your select list contains a complete set of columns already in a unique key (in the non-lookup table), 'distinct' is already guaranteed (with both current and future data) – Jack Douglas Aug 30 '11 at 9:33
  • 1
    If not you might get duplicates in the future even if there are none now as @a_horse points out in the comments below – Jack Douglas Aug 30 '11 at 15:07
5

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.

  • Unfortunately I am the php dev, although being new to the job I have no idea about how it was implemented and if it was consistentnly applied through the life cycle of the code but the group by option is a good idea and should theoretically significantly reduce the new overhead on the query. – Mark D Aug 28 '11 at 23:18
  • Developer perspective appreciated, since code presents data to the consumer of that data. +1 !!! – RolandoMySQLDBA Aug 29 '11 at 21:49
4

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

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
  • I thought of this option too but it's not the same as comparing the result sets. – Mark D Aug 28 '11 at 2:40
  • Why not? If you're selecting the same columns from the same query, the only way the counts are going to be different is if there is a duplicate that the DISTINCT filtered out. – Derek Kromm Aug 28 '11 at 2:42
  • Good point. This issue might be solved. – Mark D Aug 28 '11 at 2:51
  • 3
    But that will only proof that it's not returning duplicates now. Depending on the data that is inserted in tableB you still might get duplicates in the future. – a_horse_with_no_name Aug 28 '11 at 10:49
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.