CODEWITHSUNDEEP
mysqldatabaseperformancestored-proceduresparallel-processing
GuruKulki
GuruKulki

Reputation: 26418

Parallelism in stored procedure in mysql?

I have a stored procedure to insert few records on daily basis. Same logic gets executed for each day but in a sequential manner. So to improve the performance, I was thinking to introduce parallelism. So is there a way or could some one point me to some example where I can run some logic in a stored procedure in parallel.

EDIT: The query I am using in my stored procedure is :

INSERT INTO tmp (time_interval, cnt, dat, txn_id) SELECT DATE_FORMAT(d.timeslice, '%H:%i') as time_interval
     , COUNT(m.id) as cnt
     , date(d.timeslice) as dat
     , "test" as txn_id
  FROM ( SELECT min_date + INTERVAL n*60 MINUTE AS timeslice
           FROM ( SELECT DATE('2015-05-04') AS min_date
                       , DATE('2015-05-05') AS max_date) AS m
         CROSS
           JOIN numbers
          WHERE min_date + INTERVAL n*60 MINUTE < max_date
       ) AS d
LEFT OUTER
  JOIN mytable AS m
    ON m.timestamp BETWEEN d.timeslice
                        AND d.timeslice + INTERVAL 60 MINUTE
GROUP
    BY d.timeslice;

This query groups the records on hour basis for each day and inserts in tmp table. So I want to run this query in parallel for each day instead of sequential.

Thanks.

Upvotes: 0

Views: 718

Answers (1)

Rick James
Rick James

Reputation: 142298

Is d a set of DATETIMEs that represent the 24 hours of one day? My gut says it can be simplified a bunch. It can be sped up by adding WHERE n BETWEEN 0 AND 23. Perhaps:

SELECT '2015-05-04' + INTERVAL n*60 MINUTE AS timeslice
    FROM numbers
    WHERE n BETWEEN 0 AND 23

What is in mytable? In particular, is the 'old' data static or changing? If it is unchanging, why repeatedly recompute it? Compute only for the last hour, store it into a permanent (not tmp) table. No need for parallelism.

If the data is changing, it would be better to avoid

ON m.timestamp BETWEEN d.timeslice
                   AND d.timeslice + INTERVAL 60 MINUTE

because (I think) it will not optimize well. Let's see the EXPLAIN SELECT....

In that case, use a stored procedure to compute the start and end times and construct (think CONCAT) the ON clause with constants in it.

Back to your question... There is no way in MySQL, by itself, to get parallelism. You could write separate scripts to do the parallelism, each with its own parameters and connection.

Upvotes: 1

Related Questions