Mysql – How to use and optimize subquery on 100 million rows

MySQLperformancequery-performancesubquery

What I'm trying to do is running a job on more than 100 million domains which haven't processed before.

I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm getting a list of domains from domain table by checking their status on "domain_setting". At first it was working fine but now approximately 10 million domain processed and it's getting slower.

I know that my server is crappy for this kind of database, it has only 8 GB of memory and it's also shared with Apache web server (150 concurrent connections), some other small databases and some lightweight PHP applications. My innodb_buffer_pool_size is only 1 GB.

Anyway, I would like to learn if there is a better way or thoughts to write this query more efficiently or any other approach for doing this job and speed up the process. Here is my SQL query:

SELECT * FROM domain
WHERE domain_ID NOT IN (SELECT domain_ID FROM domain_setting) OR domain_ID IN (SELECT domain_ID FROM domain_setting WHERE is_keyword_checked = 0)
LIMIT 0,10000;

I must get domains which are not exist in domain_setting table OR has "is_keyword_checked = 0" value in domain_setting table. How can I optimize this query?

Thanks in advance 🙂

EDIT:
I can rewrite my query with LEFT JOIN as

SELECT *
FROM domain
LEFT JOIN domain_setting ON domain_setting.domain_ID = domain.domain_ID
WHERE domain_setting.is_keyword_checked = 0 OR domain_setting.is_keyword_checked IS NULL
ORDER BY domain.domain_ID ASC
LIMIT 0,10000;

but the main problem here seems full table scan. I can run it backwards by ORDER by domain.domain_ID DESC for recent added records and I can get them very fast. But still needs to scan full table if you didn't run it for a while and if recent records became more than millions of rows. So, is there any way to avoid full table scan for such work?

Best Answer

Since is_keyword_checked can take only two possible values, 0 or 1 (and it's not nullable), you can try the rewriting. There is no OR and the index on (is_keyword_checked) will be used:

SELECT * 
FROM domain
WHERE domain_ID NOT IN 
      ( SELECT domain_ID 
        FROM domain_setting 
        WHERE is_keyword_checked = 1
      ) ;

or with a NOT EXISTS subquery - which should have similar execution plan:

SELECT d.* 
FROM domain AS d
WHERE NOT EXISTS 
      ( SELECT * 
        FROM domain_setting AS ds
        WHERE ds.is_keyword_checked = 1
          AND ds.domain_ID = d.domain_ID
      ) ;

Related Solutions

SQL Server – Optimize SELECT on Subquery with COALESCE

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong).

Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages:

The optimizer has less transformation options available for FULL joins than for (the more common) INNER and LEFT joins.
The Z derived table can be removed (you can do that anyway) from the view definition.
The NOT(pe.ThisThing = 1 AND se.OtherThing = 0) will be needed only on the INNER join part.
Minor improvement, the use COALESCE() will be minimal if any at all (I assumed that se.SEId and pe.PEId are not nullable. If more columns are not nullable, you'll be able to remove more COALESCE() calls.)
More important, the optimizer may push down any conditions in your queries that involve these columns (now that COALESCE() is not blocking the push.)
All the above will give the optimizer more options to transform/rewrite any query that uses the view so it may find an execution plan that indexes on the underlying tables can be used.

In all, the view can be written as:

SELECT 
    se.SEId + '-' + pe.PEId AS Id,
    se.SEId, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    COALESCE(pe.EventType, se.EventType) AS EventType,
    pe.Duration,
    COALESCE(pe.Data, se.Data) AS Data,
    COALESCE(pe.Field, se.Field) AS Field,
    pe.ThisThing, se.OtherThing,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe INNER JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1 AND se.OtherThing = 0) 

UNION ALL

SELECT 
    '0-0',
    NULL, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    pe.EventType,
    pe.Duration,
    pe.Data,
    pe.Field,
    pe.ThisThing, NULL,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe LEFT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1)
  AND se.StaffName IS NULL

UNION ALL

SELECT 
    '0-0',
    se.SEId, NULL,
    se.StaffName, 
    se.EventTime,
    se.EventType,
    se.Duration,
    se.Data,
    se.Field,
    NULL, se.OtherThing, 
    DATEADD(minute, se.Duration, se.EventTime) AS EventEndTime
FROM PE pe RIGHT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (se.OtherThing = 0)
  AND pe.StaffName IS NULL ;

MySQL Performance – How to Optimize Slow Query with Joins and GROUP BY

OK, I ended up adding another lookup table:

CREATE TABLE IF NOT EXISTS `stops_routes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stop_id` varchar(100) NOT NULL,
  `route_id` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stop_route` (`stop_id`,`route_id`),
  KEY `stop_id` (`stop_id`),
  KEY `route_id` (`route_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Filling it was fairly fast:

mysql> insert into stops_routes (stop_id, route_id)
    ->
    -> select
    ->     s.stop_id,
    ->     r.route_id as from_route_id
    ->
    -> from routes r
    -> left join trips t on t.route_id = r.route_id
    -> left join stop_times st on st.trip_id = t.trip_id
    -> left join stops s on s.stop_id = st.stop_id
    -> group by s.stop_id, r.route_id;
Query OK, 3496 rows affected (8.38 sec)
Records: 3496  Duplicates: 0  Warnings: 0

Using it is blazingly fast:

mysql> select
    ->     r.route_id as from_route_id,
    ->     c_sr.route_id as to_route_id
    ->
    -> from routes r
    ->
    -> left join stops_routes sr on sr.route_id = r.route_id
    -> left join stop_connections c_s on c_s.from_stop_id = sr.stop_id
    -> left join stops_routes c_sr on c_sr.stop_id = c_s.to_stop_id
    ->
    -> where r.route_id <> c_sr.route_id
    -> group by r.route_id, c_sr.route_id
    -> limit 10;
+---------------+-------------+
| from_route_id | to_route_id |
+---------------+-------------+
| 0001          | 0002        |
| 0001          | 0003        |
| 0001          | 0004        |
| 0001          | 0005        |
| 0001          | 0006        |
| 0001          | 0008        |
| 0001          | 0009        |
| 0001          | 0011        |
| 0001          | 0014        |
| 0001          | 0031        |
+---------------+-------------+
10 rows in set (0.63 sec)

Now I can fill my last lookup table (set of connections between every routes on my GTFS network):

mysql> insert into route_connections (from_route_id, to_route_id)
    -> select
    ->     r.route_id as from_route_id,
    ->     c_sr.route_id as to_route_id
    ->
    -> from routes r
    ->
    -> left join stops_routes sr on sr.route_id = r.route_id
    -> left join stop_connections c_s on c_s.from_stop_id = sr.stop_id
    -> left join stops_routes c_sr on c_sr.stop_id = c_s.to_stop_id
    ->
    -> where r.route_id <> c_sr.route_id
    -> group by r.route_id, c_sr.route_id;
Query OK, 2848 rows affected (0.31 sec)
Records: 2848  Duplicates: 0  Warnings: 0

Amazingly fast. I guess the engine couldn't break up the steps to optimize this.
I'd still be interested to know if it would be possible to get the same result (from route to route connections table) using only one sub-second or sub-minute query.

Best Answer

Related Solutions

SQL Server – Optimize SELECT on Subquery with COALESCE

MySQL Performance – How to Optimize Slow Query with Joins and GROUP BY

Related Question