How can I optimize this MySQL query? Should I use joins?

Question

I would like to optimize the following MySQL Query, It is picking data from 2 tables where authorchecklist table has records in details while selectedrevlist table is using for the reviewer's reviews.

I want to get those records from selectedrevlist where scoreSubmit = 1

SELECT  *
    FROM  `authorchecklist` acl
    WHERE  acl.manuscriptStatus = 'Awaiting Reviewer Assignment'
      AND  acl.submitStatus = '1'
      AND  
        ( SELECT  COUNT( 1 )
            FROM  selectedrevlist srl
            WHERE  srl.OrderNumber = acl.OrderNumber
              AND  srl.editorType = 'Editor'
              AND  srl.editorID = '10'
              AND  srl.scoreSubmit = '1' 
        ) = 1

The above query is working fine but it takes aprox 20 seconds to load the records.

Could you please add table structure and some insert queries in both table ? — Alpesh Jikadra, Apr 01 '18 at 14:25
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. For SQL performance that includes EXPLAIN results & statistics. (Constraints, indexes & plans are critical.) Please research before considering posting. That includes basics of SQL optimization/performance--immediately leading to indexes, plans, statistics & SARGability. [ask] — philipxy, May 01 '20 at 16:31

score 1 · Answer 1 · answered Apr 01 '18 at 14:15

1

This is your query:

SELECT acl.*
FROM authorchecklist acl
WHERE acl.manuscriptStatus = 'Awaiting Reviewer Assignment' AND
      acl.submitStatus = 1 AND
      (SELECT COUNT(1) 
       FROM selectedrevlist srl 
       WHERE srl.OrderNumber = acl.OrderNumber AND
             srl.editorType =  'Editor' AND
             srl.editorID = 10 AND
             srl.scoreSubmit = 1
      ) = 1 ;

For this query, you want indexes on authorchecklist(submitStatus, manuscriptStatus, OrderNumber) and selectedrevlist(OrderNumber, editorId, scoreSubmit).

answered Apr 01 '18 at 14:15

Gordon Linoff

1,242,037
58
646
786

With the index authorchecklist(submitStatus, manuscriptStatus, OrderNumber), he will need to change the query in order to use the index. The acl.submitStatus should be compared first (the order of the comparisons should match the fields order in the indexes). I'm also pretty sure he doesn't need to index, for instance, the scoreSubmit and editorID. – Pedro Amaral Couto Apr 01 '18 at 14:31
@PedroAmaralCouto Could you provide a reference for the claim that query order must match index order? Because, I think you may be misinterpretting the leftmost principle of index order c.f., https://stackoverflow.com/q/2292662/2908724 – bishop Apr 01 '18 at 14:59
@PedroAmaralCouto . . . That is simply false. The order of the equality conditions in the `WHERE` is irrelevant. They can match an index in either order. – Gordon Linoff Apr 01 '18 at 19:25
2

@bishop, it seems that you're right: https://stackoverflow.com/questions/1252279/mysql-indices-and-order "The order of conditions WITHIN the query does not matter" – Pedro Amaral Couto Apr 01 '18 at 21:49
Count the `COUNT` come back with some value more than 1? – Rick James Apr 07 '18 at 20:24

Pedro Amaral Couto · Answer 2 · 2018-04-01T21:50:59.800

I rearranged the query to make it easier to read:

SELECT *
FROM  `authorchecklist` acl
WHERE acl.manuscriptStatus =  'Awaiting Reviewer Assignment'
      AND acl.submitStatus = '1'
      AND (
          SELECT COUNT( 1 )
          FROM selectedrevlist srl 
          WHERE srl.OrderNumber = acl.OrderNumber
          AND  srl.editorType =  'Editor' 
          AND  srl.editorID =  '10' AND srl.scoreSubmit = '1'
      ) = 1

I'm assuming there's only one selectedrevlist for each authorchecklist.

You didn't send the tables definitions ("CREATE TABLE ..."), but probably at least one of those fields are not indexed:

authorchecklist.manuscriptStatus
selectedrevlist.OrderNumber

If they're not indexed, the SQL server will need to transverse all the records. It will transverse all the authorchecklist rows, and for each authorchecklist row, it will transverse all the selectedrevlist rows to find the "srl.OrderNumber = acl.OrderNumber". Indexes may make the insertions a bit slower, but they speed up readings if they're used appropriately.

[removed wrong assertion]

If you're using MySQL, add the "LIMIT 1" when you're sure that there will always be fetched only one record. Also, use the InnoDB engine and add foreign keys - those are indexes that validate the relations.

Have a look at these:

Thanks for your reply, actually I was looking for such a query which get records having 1, 2 or 3 responses. For Example I want to get those messages which are having 1, 2, 3 replies. — M. Asif Khan, Apr 06 '18 at 06:56
This is because I want to display counter against each record, and when click on counter It should display the 1, 2 or 3 records in detail. I hope you got my point. — M. Asif Khan, Apr 06 '18 at 06:57

score 0 · Answer 3 · answered Apr 03 '18 at 20:07

Add these indexes:

ALTER TABLE
  `authorchecklist`
ADD
  INDEX `authorchecklist_idx_manuscriptstatu_submitstatus` (`manuscriptStatus`, `submitStatus`);

ALTER TABLE
  `authorchecklist`
ADD
  INDEX `authorchecklist_idx_ordernumber` (`OrderNumber`);

ALTER TABLE
  `selectedrevlist`
ADD
  INDEX `selectedrevlist_idx_editort_editori_scoresu_ordernu` (
    `editorType`,
    `editorID`,
    `scoreSubmit`,
    `OrderNumber`
  );

Use EXISTS instead of a subquery that counts records. Exists subquery will exit once something is found, instead of counting all rows matching the filters.

SELECT 
    *
FROM
    `authorchecklist` acl
WHERE
    acl.manuscriptStatus = 'Awaiting Reviewer Assignment'
        AND acl.submitStatus = '1'
        AND EXISTS (SELECT *
        FROM
            selectedrevlist srl
        WHERE
            srl.OrderNumber = acl.OrderNumber
                AND srl.editorType = 'Editor'
                AND srl.editorID = '10'
                AND srl.scoreSubmit = '1')

How can I optimize this MySQL query? Should I use joins?

3 Answers3