Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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
    
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

6 Answers 6

up vote 4 down vote accepted

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.

share|improve this answer
    
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

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

share|improve this answer
    
That's a good idea thanks. –  Mark D Aug 28 '11 at 23:20

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
share|improve this answer
    
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
2  
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

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.

share|improve this answer

you 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.

share|improve this answer
    
One more thing .. 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. –  Razvan Popovici Aug 29 '11 at 20:36

When you want to check the data from two distinct select statements I'd save their data in 2 physical tables and then compare them with a checksum table command. An example can be seen here: Using Checksums to Ensure Table Consistency in MySQL.

Or you can use some visual tools for comparison. Examples can be found in the following SO question: MySQL Diff Tool.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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