MySQL Outfile very slow

Question

this is my first post and i really need help with this...... i got this escenario....

1. Export data from dbf files
2. Save dbf files data into plain file with all values delimited by ","
3. Then i depurate the data and take only the fielda i need
4. Take those fields name and create the tables on mysql
5. Import the data into mysql (load data infile)
6. Process data and get calculated values
7. Generate csv with those values

So.... until step 6 alls good, but when i try to export the query everything go wrong, the data never out.... about 2 days running and nothing happens.... still processing but no output

This is the query:

SELECT (DATE_FORMAT(NOW(), '%Y%m%d')),'0001391343',
IFNULL(LPAD(UPPER(TCPCB.CODICLIE), 10, '0'), "          "),'VE0801',
(SELECT IFNULL(LPAD(REPLACE(IFNULL(SUM(C.MONTO),0), '.', ''), 15, '0'),
   '000000000000000') FROM TCPCB C WHERE C.SIGNO = "D" AND C.CODICLIE = TCPCB.CODICLIE),
(SELECT IFNULL(LPAD(REPLACE(IFNULL(SUM(C.MONTO),0), '.', ''), 15, '0'),
   '000000000000000') FROM TCPCB C WHERE C.SIGNO = "C" AND C.CODICLIE = TCPCB.CODICLIE),
(SELECT IFNULL(LPAD(REPLACE(((SELECT IFNULL(SUM(C.MONTO),0) FROM TCPCB C 
   WHERE C.SIGNO = "D" AND C.CODICLIE = TCPCB.CODICLIE)-(SELECT IFNULL(SUM(C.MONTO),0)
   FROM TCPCB C WHERE C.SIGNO = "C" AND C.CODICLIE = TCPCB.CODICLIE)), '.', ''), 15, '0'),
   '000000000000000')),
IFNULL(LPAD(UPPER(TCPCB.SIGNO), 1, ' '), " "),
IF(((((SELECT IFNULL(SUM(C.MONTO),0) FROM TCPCB C WHERE C.SIGNO = "D" AND C.CODICLIE = TCPCB.CODICLIE)-
   (SELECT IFNULL(SUM(C.MONTO),0) FROM TCPCB C WHERE C.SIGNO = "C" AND C.CODICLIE = TCPCB.CODICLIE))*
   DAY(LAST_DAY(NOW())))/
   (SELECT IFNULL(SUM(TOTADOCU),0) FROM TFACHISA WHERE TIPODOCU = "FA" AND CODICLIE = TCPCB.CODICLIE)-
   (SELECT IFNULL(SUM(TOTADOCU),0) FROM TFACHISA WHERE TIPODOCU = "DV" AND CODICLIE = TCPCB.CODICLIE)) < 0, "   0",
   LPAD(ROUND(((((SELECT IFNULL(SUM(C.MONTO),0) FROM TCPCB C WHERE C.SIGNO = "D" AND C.CODICLIE = TCPCB.CODICLIE)-
   (SELECT IFNULL(SUM(C.MONTO),0) FROM TCPCB C WHERE C.SIGNO = "C" AND C.CODICLIE = TCPCB.CODICLIE))*
   DAY(LAST_DAY(NOW())))/
   (SELECT IFNULL(SUM(TOTADOCU),0) FROM TFACHISA WHERE TIPODOCU = "FA" AND CODICLIE = TCPCB.CODICLIE)-
   (SELECT IFNULL(SUM(TOTADOCU),0) FROM TFACHISA WHERE TIPODOCU = "DV" AND CODICLIE = TCPCB.CODICLIE)),0), 4, " "))
INTO OUTFILE 'C:\\Users\\username\\Desktop\\gg.csv'
    FIELDS TERMINATED BY ';'
    ENCLOSED BY ''
    ESCAPED BY ''
    LINES TERMINATED BY '\n'
FROM TCPCB
INNER JOIN TFACHISA ON TFACHISA.CODICLIE = TCPCB.CODICLIE
WHERE 1 GROUP BY TCPCB.CODICLIE

The thing about this is.... if i delete the outfile line alls good, get the results in 7 secs.... (1.844 d / 7.703 f).... the engine is MyISAM

PD: use myisam because i only need read data and process values, then i delete everything and keep the csv... excuse my english...

Thanks!!

Update:

The process now works was the conf on the server side, try fresh install and alls good but now when i use where conditions got lost connection, the query never return data.... only with where conditions or inner joins.

Solved:

Long time but finally solved, using MyISAM and using multiple-index in the main fields.... However, a stored procedure was good idea here... Thanks!!

Answer 1

I had a similar issue, it was taking too long because it was writing the results into the file as it ran the query, which is extremely slow, specially if the hard disk where you wanna dump the CSV is the same as where the database is stored.

Solution was simply to pack the original query into another SELECT query.

So, instead of

SELECT * 
FROM tableA a INNER JOIN tableB b 
ON (a.b_id = b.id) 
INTO OUTFILE 'C:\\Users\\username\\Desktop\\gg.csv'

use

SELECT *
FROM (SELECT *
      FROM tableA a INNER JOIN tableB b 
      ON (a.b_id = b.id)) tm 
INTO OUTFILE 'C:\\Users\\username\\Desktop\\gg.csv'

This makes sure that you first run your query and then write everything into the file. There should be a better solution like just changing a config variable that avoids writing through the disk every time, but this worked for me already.