speed up mysql export

[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")

 

$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.

[effigy]

Can you not use joins to get the additional information?

[h_razack]

Can we do conditional things with join?

The "contact" can be of different type, and I get different information depending on the type.

 

But you're right, maybe I should look into mysql a bit more to have less db calls.

 

Thanks for the pointer.

[effigy]

Perhaps; we'll need more info.

[h_razack]

So, to be more precise, I have 5 tables:

 

• contact
  
• contact_individual
  
• contact_organisation
  
• address
  
• phone
  

 

And I have an array of contact ids.

 

For each of these contacts I need to extract some info from the above tables. Then I gather everything in a csv file that I output.

 

The logic is:

 

for each contact

 

• 
   
• if contact is an individual (there is a contact_type_id field in the contact table), get first name, last name, and email from table contact_individual
   
• if contact is an organisation, get name, website and email from table contact_organisation
   
• get addresse(s) from table address
   
• get phone(s) from table phone
   
• put all these info in a nice comma delimited line
   
• add the line to all the previous lines (for previous contacts)
  

 

then export the whole lot in a csv file.

 

[effigy]

Do the addresses and phones have corresponding ids in the contacts table?

[h_razack]

No, it's the other way round. There is a contact_id field in both phones and addresses tables (because a contact can have several phones or addresses).

 

 

[effigy]

Right, but they refer to the id of a record in contacts?

[h_razack]

Oh, I see. Yes, they do.

[effigy]

How are you merging the individual and organization data together? Which data ends up in the same fields?

Is there any data in the contact table that you want?