speed up mysql export

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
h_razack
Forum Newbie
Posts: 2
Joined: Mon Feb 18, 2008 3:09 pm

speed up mysql export

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: speed up mysql export

Post 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') .
(#10850)
h_razack
Forum Newbie
Posts: 2
Joined: Mon Feb 18, 2008 3:09 pm

Re: speed up mysql export

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: speed up mysql export

Post 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.
(#10850)
Post Reply