0

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 ?

8
  • Please provide `SHOW CREATE TABLE for each table so we can verify that you have the best index.
    – Rick James
    Jan 13, 2023 at 1:50
  • And, how big are the tables (approximate number of rows)?
    – Rick James
    Jan 13, 2023 at 1:51
  • We need to know what table TopMz1, etc, are in.
    – Rick James
    Jan 13, 2023 at 2:09
  • Does the COUNT(*) look correct? Or is it grossly inflated due to the JOINs?
    – Rick James
    Jan 13, 2023 at 2:11
  • How many subqueries? What is the time for one subquery? (The whole query takes 2.00 seconds?). How many rows in the resultset?
    – Rick James
    Jan 13, 2023 at 2:27

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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