Page 1 of 1

speed up mysql export

Posted: Mon Feb 18, 2008 3:11 pm
by h_razack
Hi all,

I have a function that loops through lots of table, gathering various information, and that exports the whole lot in csv format. It works ok, but the problem is that it's really slow.

Here is what it does in a nutshell (I am using a mysql library called "ezsql")

Code: Select all

 
$contacts = $db->get_results('SELECT * FROM contact');
foreach($contacts as $contact) {
     $row = array();
     // get address for this contact from the address table
     // get phone numbers from phone table
     // get lots of stuff from other tables
     $row[] = $info1;
     $row[] = $info2;
     // ... and I keep on adding things
     $csv_output .= join(',', $row)."\n";
}
$filename = "cbt_user_db.csv";
header('Content-Type: text/x-csv');
header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
if (PMA_USR_BROWSER_AGENT == 'IE') {
      header('Content-Disposition: inline; filename="' . $filename . '"');
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      header('Pragma: public');
} 
else {
      header('Content-Disposition: attachment; filename="' . $filename . '"');
      header('Pragma: no-cache');
}
print $csv_output;
 
So, the idea is quite straightforward, but it doesn't scale well. For more than 1000 contacts it is disgunstingly slow...

Does anyone have any suggestion on how to optimize this?

Many thansk,

Hubert.

Re: speed up mysql export

Posted: Mon Feb 18, 2008 3:17 pm
by Christopher
For speed you could try "SELECT * FROM contact INTO OUTFILE 'cbt_user_db.csv'" and then file_get_contents('cbt_user_db.csv') .

Re: speed up mysql export

Posted: Mon Feb 18, 2008 3:37 pm
by h_razack
The problem is that there is some conditional things when I gather the info. The contact can be of different type, and depending of the type I get different info.

I suppose I would need to really minimize the db calls. Maybe I could use join, although I don't know much about it. Can we do conditional stuff with that?

Thanks for the pointer, though.

Re: speed up mysql export

Posted: Mon Feb 18, 2008 4:24 pm
by Christopher
You can use "INTO OUTFILE" with any SELECT syntax. The difference is that you are letting MySQL generate the file within one query -- that is generally faster. Try it as see which is faster.