Mysql join heavy query on large tables taking hours

join;MySQLperformancequery-performance

I'm trying to query our entire database to build a report and I've run into the problem that I have queries like the one below that are taking near 2 hours to run and some take even longer. I've changed the some of the names but the structure is the same.

Person_table has about 5 million rows

check_table is a subset of Person_table and has about 3 million rows

event_table has about 20 million rows

update  check_table as CT
    inner join  
        ( SELECT  B1.id
            from  Person_table as PT
            inner join  event_table as B1  ON PT.id = B1.person_id
            inner join  event_type_table as E using (code_id)
            inner join  
                ( SELECT  *
                    from  event_table as ET
                    where  ET.code_id in ('XYZ','ABC','DEF','ETC.')
                ) as F  ON ET.id = F.person_id
                       and  B1.Event_date = F.event_date
        ) as B2 using (id) set flag_1=1;

I've tried to get an explain on many of the queries I have to do but it always spits back a "Explain Data not available for statement".

Best Answer

It may (or may not) be possible to add some composite indexes to speed up the query. It may (or may not) be possible to reformulate the query to speed it up -- perhaps by turning subqueries into JOINs.

But it seems like 3M possible updates will lead to a very slow query in any case. I suggest chunking the UPDATE into no more than 1000 rows at a time. This will avoid various locks that interfere with other traffic. And it may actually run faster -- due to not needing to hang onto millions of rows for potential ROLLBACK.

Discussion of chunking: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

If you want me to check your indexes, please provide SHOW CREATE TABLE.

Related Solutions

How to the LEFT JOIN return more results than the unjoined query

Each row in the first table may match more than one row in the second table.

You can check this with:

SELECT l.id,count(*) 
FROM list l 
LEFT JOIN cardinal c ON l.id=c.id 
WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink' 
group by l.id 
having count(*)>1;

A little demo is on SQL Fiddle here.

Mysql improve Self join performance large table

Here's a stab at this: see fiddle at http://sqlfiddle.com/#!9/1fc60/1. My algorithm in general to take all the start/end values for all clients, sort those numbers and make each adjacent pair of values into an interval. For n clients, you have 2n-1 intervals (in worst case). Then, evaluate each of those intervals to see how many clients' IP ranges they fall into. If more than 1, then you found a conflicting IP range between two clients. It does works, but as you get bigger data sets, it gets considerably slower. The algorithm is Polynomial in time complexity. On modest hardware, I was able to process 16k clients for conflicts in about 6 minutes.

I think there's a much better algorithm that might take logarithmic time (good), or even linear time (way better), but this algo would be pretty tough to do in MySQL or any RDBMS. It would be much easier in an OO 3GL like Java or .NET.

Best Answer

Related Solutions

How to the LEFT JOIN return more results than the unjoined query

Mysql improve Self join performance large table

Related Question