[SOLVED] improve mysql select query with order by option

Issue

I have following table with around 10 million records.
and using following query to retrieve data, but it is taking more than 4, 5 seconds to hand over the response.

Is any way to improve query…?

CREATE TABLE `master` (
      `organizationName` varchar(200) NOT NULL DEFAULT '',
      `organizationNameQuery` varchar(200) DEFAULT NULL,
      `organizationLinkedinHandle` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
          `organizationDomain` varchar(110) NOT NULL DEFAULT '',
          `source` varchar(10) NOT NULL DEFAULT '',
          `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY `master_inx` (`organizationName`(80),`organizationDomain`(80),`organizationLinkedinHandle`(80),`organizationNameQuery`(80),`source`),
          KEY `organizationDomain` (`organizationDomain`),
          KEY `domainWithModified` (`organizationDomain`,`modified`),
          KEY `modifiedInx` (`modified`)
        );

Query:

SELECT * 
FROM (SELECT * 
      FROM Organizations.master 
      where ( ( organizationDomain like 'linkedin.com' 
                || organizationNameQuery = 'linkedin.com') 
              and source like 'MY_SOURCE') ) M 
ORDER BY M.modified DESC limit 1;

1 row in set (4.69 sec)

UPDATE

I found by breaking OR operator i am getting result faster.

For example:

SELECT * 
FROM (SELECT * 
      FROM Organizations.master 
      where ( ( organizationDomain like 'linkedin.com') 
              and source like 'MY_SOURCE') ) M 
ORDER BY M.modified DESC limit 1;
1 row in set (0.00 sec)

    SELECT * 
    FROM (SELECT * 
          FROM Organizations.master 
          where ( (organizationNameQuery = 'linkedin.com') 
                  and source like 'MY_SOURCE') ) M 
    ORDER BY M.modified DESC limit 1;
1 row in set (0.00 sec)

Solution

Use OR, not || in that context.

The performance villain is OR. Turn the OR into UNION:

( SELECT * 
    FROM Organizations.master 
    WHERE organizationDomain = 'linkedin.com' 
      AND source = 'MY_SOURCE'
    ORDER BY modified DESC limit 1
) UNION ALL
( SELECT * 
    FROM Organizations.master 
    WHERE organizationNameQuery = 'linkedin.com' 
      AND source = 'MY_SOURCE'
    ORDER BY modified DESC limit 1
}
ORDER BY modified DESC LIMIT 1;

Notes:

• This formulation is likely to take about 0.00s to run.

• The ORDER BY and LIMIT shows up 3 times.

• If you need OFFSET, things get a little tricky.

• Change back to LIKE if you allow users to enter wildcards.

• A leading wildcard would not be efficient.

• UNION ALL is faster than UNION (aka UNION DISTINCT).

• It needs two new composite indexes; the order of the 2 columns is not critical:

  INDEX(organizationDomain, source),
  INDEX(organizationNameQuery, source)
  

Answered By – Rick James

Answer Checked By – David Marino (BugsFixing Volunteer)