2

We have a csv file with 2000 rows and a database table1 about 2 million rows and another table2 with 60000 rows.

We need to query from table1 based on parameters from the csv file, so each row in csv should execute one select query. Initially we tried to execute the following query within a for loop in the application:

SELECT table1.c1, 
       table1.c2, 
       ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]), 
       point(table1.c3, table1.c4)) * 0.2 AS length, 
       table1.c5 
FROM   table1 
WHERE  table1.c1 IN (SELECT DISTINCT table2.c1 
                     FROM      table2 
                     LEFT JOIN table2.c1=table3.c1 
                     WHERE table2.c2=1 
                     AND   table2.c3 BETWEEN 1000 AND 2000) 
HAVING length < csv.c4[i] 
AND    table1.c5 BETWEEN date("start_date") AND date("end_date") 
ORDER BY table.c1

the csv.c1[i] i is actually the loop index. Because of the 2000 round trips to MySQL server, it takes a long time to complete this. It takes about 16 hours just to get through the queries.

So, I wrote the SP below to avoid looping and simply call this SP so can do the looping in MySQL server:

CREATE PROCEDURE sp() 
    BEGIN 
      DECLARE arg0 VARCHAR(255);
      DECLARE arg1 FLOAT;
      DECLARE arg2 FLOAT;
      DECLARE arg3 FLOAT;
      DECLARE cur1 CURSOR FOR SELECT * FROM csv_based_table;
      OPEN cur1; 
      read_loop: LOOP
         FETCH cur1 INTO arg0, arg1, arg2, arg3;
         SELECT col0, 
                col1, 
                ST_Distance_Sphere(point(arg1, arg2),point(col3,col4))*2 AS length, 
                arg0 
         FROM   important_table1 
         WHERE  col0 IN (SELECT DISTINCT c0 
                         FROM   important_table2 
                         LEFT JOIN table3 
                         ON col0 = c0 
                         WHERE c1=1 
                         and c2 BETWEEN 1000 AND 2000) 
         HAVING distance < arg3 
         AND col5 BETWEEN date("start_date") AND date("end_date") 
         ORDER BY arg0, col0;
      END LOOP;
      CLOSE cur1;
    END;

So, instead of using the csv file, we create a database table for that csv file and run the above stored procedure. Problem is this turned out to be slower than the original 2000 iterations loop. In a test over 100 csv rows, for loop-based original solution finished in 317.6 seconds while the stored procedure took 321.5 seconds just for the SP itself. Why is this happening and how can I optimize this?

  • It is hard to help when we can't see what column is in what table, for example: WHERE c1=1 – Rick James Dec 31 '17 at 0:43
  • That's about a 1% difference -- not significant. What is means is that CSV vs CROSS JOIN is not the main part of the inefficiency. – Rick James Dec 31 '17 at 1:12
2

Here's one suggestion:

CREATE TEMPORARY TABLE csv
( i int not null
, c1 ... not null
, c2 ... not null
, c3 ... not null
, c4 ... not null )

Load the csv file into the temporary table, and use a join to get the result.

SELECT csv.i 
   table1.c1, 
   table1.c2, 
   csv.st_dist_spere, 
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),  
                      point(table1.c3, table1.c4)) * 0.2 AS length, 
   table1.c5 
FROM   table1
CROSS JOIN csv  
WHERE  table1.c1 IN (
                 SELECT DISTINCT table2.c1 
                 FROM      table2 
                 LEFT JOIN table2.c1=table3.c1 
                 WHERE table2.c2=1 
                 AND   table2.c3 BETWEEN 1000 AND 2000
HAVING length < csv.c4 
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date) 
ORDER BY csv.i, table.c1

I would then look at the IN predicate (I'll assume this is what it is supposed to be):

SELECT DISTINCT table2.c1 
FROM      table2 
LEFT JOIN table3
    ON table2.c1=table3.c1 
WHERE table2.c2=1 
  AND table2.c3 BETWEEN 1000 AND 2000 

The outer join against table3 can be eliminated (a smart optimizer should realize this, but I don't know if this is implemented in MySQL):

SELECT DISTINCT table2.c1 
FROM      table2 
WHERE table2.c2=1 
  AND table2.c3 BETWEEN 1000 AND 2000

This leaves us with:

SELECT csv.i 
   table1.c1, 
   table1.c2, 
   csv.st_dist_spere, 
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),  
                      point(table1.c3, table1.c4)) * 0.2 AS length, 
   table1.c5 
FROM   table1
CROSS JOIN csv  
WHERE  table1.c1 IN (
                 SELECT DISTINCT table2.c1 
                 FROM      table2 
                 WHERE table2.c2=1 
                 AND   table2.c3 BETWEEN 1000 AND 2000
       )
HAVING length < csv.c4 
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date) 
ORDER BY csv.i, table.c1

Next step, is trying to replace the IN PREDICATE with a JOIN:

SELECT csv.i 
   table1.c1, 
   table1.c2, 
   csv.st_dist_spere, 
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),  
                      point(table1.c3, table1.c4)) * 0.2 AS length, 
   table1.c5 
FROM   table1
CROSS JOIN csv  
JOIN ( SELECT DISTINCT table2.c1 
       FROM      table2 
       WHERE table2.c2=1 
       AND   table2.c3 BETWEEN 1000 AND 2000
     ) as X
   ON X.c1 = table1.c1
HAVING length < csv.c4 
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date) 
ORDER BY csv.i, table.c1 

It may be advantageous to push the range predicate into table1 before doing the cross join:

SELECT csv.i 
   table1.c1, 
   table1.c2, 
   csv.st_dist_spere, 
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),  
                      point(table1.c3, table1.c4)) * 0.2 AS length, 
   table1.c5 
FROM (  
    select ... from table1 
    where table1.c5 BETWEEN date(start_date) AND date(end_date)
) as table1
CROSS JOIN csv  
[....]
  • Sorry, I had made a mistake, the LEFT JOIN in the inner SELECT was with a third table. – swdon Dec 29 '17 at 13:27
  • I have at least 17 million rows in table 1 before any filtering and 2000 rows in csv. Will the cross join produce something very big? This is safe to do? – swdon Dec 29 '17 at 14:51
  • You can push the predicates into table1 before the join – Lennart Dec 29 '17 at 15:10
2

(Lennart covers some issues; I'll cover some others.)

16 hours is not bad for performing trillions of operations.

Turn the inefficient IN ( SELECT ... ) into a derived table.

table1.c1 IN ( SELECT DISTINCT table2.c1 
                 FROM      table2 
                 LEFT JOIN table2.c1=table3.c1 
                 WHERE table2.c2=1 
                 AND   table2.c3 BETWEEN 1000 AND 2000 )

-->

JOIN ( SELECT DISTINCT c1 FROM table2
                 WHERE c2 = 1 AND c3 BETWEEN ... ) AS x
    ON x.c1 = table1.c1

(Lennart explains most of why.) Also, depending on what version you are using, the INner SELECT may be repeated for each row of c1.

Add some INDEXes. This should significantly decrease the 4 billion computations.

Details: have the composite INDEX(c2, c3) -- in that order. Also, INDEX(c1) on table1.

It's not just 2000 round trips, it's 2000*2M "rows" involved. That's 4 billion!

Move this out of HAVING and into WHERE:

AND    table1.c5 BETWEEN date("start_date") AND date("end_date") 

and have INDEX(c5) on table1. Doing this should cut down the 2M (hence, the 4B) rows by some amount. If "start_date" is not a literal, but rather a column in some table, then we have a another problem.

Consider a 'bounding box'.

I'm guessing you are trying to list which of 2M items are "close" to 2K items.

One way to help (but not fully optimize) a "find nearest" in a two-dimensional problem is to have a "bounding box" to quickly filter out many of the rows. I don't know your schema well enough to give you more than a rough idea:

SELECT ... AS distance
    WHERE lat BETWEEN 12.345 AND 13.579
      AND lng BETWEEN 45.678 AND 47.474
    HAVING distance < ...

and then have INDEX(lat, lng) and INDEX(lng, lat). (The optimizer will pick the one that seems better for the data in question.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.