improve mysql select query with order by option-CodePudding

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)

CodePudding user response：

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)
```

CodePudding user response：

As I checked the query I think you can remove the like operator and use =.

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