Replication issue - CREATE SELECT alternative?

Question

I've an MySQL 5.1 slave for our BI team.

They need to make some CREATE SELECT with big select queries (several million lines).

As CREATE SELECT is a DDL, if the replication attempts to update some rows in same tables than the SELECT statement, replication is blocked until the freeing of the CREATE SELECT.

Do you now a good non-blocking alternative to thoses CREATE SELECT statements?

I thought to an SELECT INTO OUTPUT FILE then LOAD DATA INFILE but they will fill out our disks as BI guys like to do... :)

Max.

Answer 1

If you have to perform DDL, do it just to create the table.

For example, suppose you have the following query involving two InnoDB tables:

CREATE TABLE hiringdata
SELECT a.id,a.name,b.hiredate,b.salary
FROM employee a INNER JOIN hrinfo b
ON A.id = B.empid;

Of course, you should expect some pause to

1. create the table
2. execute the query
3. load results info

How you ever considered separating step 1 from the other two steps? All it takes is a simple WHERE clause and an INSERT INTO query:

CREATE TABLE hiringdata
SELECT a.id,a.name,b.hiredate,b.salary
FROM employee a INNER JOIN hrinfo b
ON A.id = B.empid
WHERE 1 = 2;
INSERT INTO hiringdata
SELECT a.id,a.name,b.hiredate,b.salary
FROM employee a INNER JOIN hrinfo b
ON A.id = B.empid;

There you have it: DDL is detached from the SELECT query. This way, any transactions that caches data changes in the Transaction Logs are not held hostage by the CREATE TABLE.

Give it a Try !!!