How do I improve performance of select count(*) from sub query order by desc

Question

To the database experts, I have a query like this:

SELECT COUNT(*) as Common, TempTable.Id FROM (
SELECT Spectrum.Id FROM Spectrum LEFT JOIN File ON File.Id = Spectrum.FileId LEFT JOIN Submission ON Submission.Id = File.SubmissionId LEFT JOIN UserPrincipal ON UserPrincipal.Id = Submission.UserPrincipalId
 WHERE (TopMz1 > 90.990000 AND TopMz1 < 91.010000) OR (TopMz2 > 90.990000 AND TopMz2 < 91.010000) OR (TopMz3 > 90.990000 AND TopMz3 < 91.010000) OR (TopMz4 > 90.990000 AND TopMz4 < 91.010000) OR (TopMz5 > 90.990000 AND TopMz5 < 91.010000) OR (TopMz6 > 90.990000 AND TopMz6 < 91.010000) OR (TopMz7 > 90.990000 AND TopMz7 < 91.010000) OR (TopMz8 > 90.990000 AND TopMz8 < 91.010000)) 
union all
SELECT Spectrum.Id FROM Spectrum LEFT JOIN File ON File.Id = Spectrum.FileId LEFT JOIN Submission ON Submission.Id = File.SubmissionId LEFT JOIN UserPrincipal ON UserPrincipal.Id = Submission.UserPrincipalId
WHERE ((TopMz1 > 446.990000 AND TopMz1 < 447.010000) OR (TopMz2 > 446.990000 AND TopMz2 < 447.010000) OR (TopMz3 > 446.990000 AND TopMz3 < 447.010000) OR (TopMz4 > 446.990000 AND TopMz4 < 447.010000) OR (TopMz5 > 446.990000 AND TopMz5 < 447.010000) OR (TopMz6 > 446.990000 AND TopMz6 < 447.010000) OR (TopMz7 > 446.990000 AND TopMz7 < 447.010000) OR (TopMz8 > 446.990000 AND TopMz8 < 447.010000) OR (TopMz9 > 446.990000 AND TopMz9 < 447.010000)) 
union all 
SELECT Spectrum.Id FROM Spectrum LEFT JOIN File ON File.Id = Spectrum.FileId LEFT JOIN Submission ON Submission.Id = File.SubmissionId LEFT JOIN UserPrincipal ON UserPrincipal.Id = Submission.UserPrincipalId
WHERE ((TopMz1 > 104.990000 AND TopMz1 < 105.010000) OR (TopMz2 > 104.990000 AND TopMz2 < 105.010000) OR (TopMz3 > 104.990000 AND TopMz3 < 105.010000) OR (TopMz4 > 104.990000 AND TopMz4 < 105.010000) OR (TopMz5 > 104.990000 AND TopMz5 < 105.010000) OR (TopMz6 > 104.990000 AND TopMz6 < 105.010000) OR (TopMz7 > 104.990000 AND TopMz7 < 105.010000) OR (TopMz8 > 104.990000 AND TopMz8 < 105.010000) OR (TopMz9 > 104.990000 AND TopMz9 < 105.010000) OR (TopMz10 > 104.990000 AND TopMz10 < 105.010000)) 
union all
...
) AS TempTable
GROUP BY Id ORDER BY Common DESC

The ... means there will be similar sub query like this with different topmz values. If I run the inner query alone it is very fast but with the count() and group by in the outer query it is much slower. Basically, how can I speed up select count() group by from a result set? as I cannot add index to it. Right now with proper index (I think) on the table the query executed in 2 second. How can I improve this time ?

Answer 1

A guess...

Assuming that TopMz* is in UserPrincipal, then

    FROM  Spectrum
    LEFT JOIN  File  ON File.Id = Spectrum.FileId
    LEFT JOIN  Submission  ON Submission.Id = File.SubmissionId
    LEFT JOIN  UserPrincipal  ON UserPrincipal.Id = Submission.UserPrincipalId
    WHERE TopMz...

needs

UserPrincipal: INDEX(TopMz1, TopMz2, TopMz3, TopMz4, TopMz5, 
                     TopMz6, TopMz7, TopMz8, TopMz9)
Submission:    INDEX(UserPrincipalId)
File:          INDEX(SubmissionId)
Spectrum:      INDEX(FileId)

That should convince the Optimizer to do that nasty WHERE first and move on to the other tables in the opposite order.

Can you explain what TopMz1 (etc) represents? I may be able to provide some mathematical trick to speed it up. The suggested INDEXes above still lead to a full index scan on UserPrincipal. Not cheap.

Also, if the TopMz columns are in UserPrincipal, then LEFT is unnecessary.

I assume you are using ENGINE=InnoDB on the tables?

Can you toss the UNIONs and combine the WHEREs into

            WHERE  (TopMz1 > 90.99  ... OR TopMz2 ... OR ...)
               OR  (TopMz1 > 446.99 ... OR TopMz2 ... OR ...)
               OR ...

It won't help the speed (but just keystroke count):

( TopMz1 > 90.990000  AND  TopMz1 < 91.010000 )

into

ABS(TopMz1 - 91) < 0.01

I believe that the subqueries are where the time is spent. That is, the COUNT/GROUP/ORDER is only a minor part of the total.