[FIXED] Speed up csv export when using php from mysql database query

Issue

Ok, so i've got a web system (built on codeigniter & running on mysql) that allows people to query a database of postal address data by making selections in a series of forms until they arrive at the selection that want, pretty standard stuff. They can then buy that information and download it via that system.

The queries run very fast, but when it comes to applying that query to the database,and exporting it to csv, once the datasets get to around the 30,000 record mark (each row has around 40 columns of which about 20 are all populated with on average 20 chars of data per cell) it can take 5 or so minutes to export to csv.

So, my question is, what is the main cause for the slowness? Is it that the resultset of data from the query is so large, that it is running into memory issues? Therefore should i allow much more memory to the process? Or, is there a much more efficient way of exporting to csv from a mysql query that i'm not doing? Should i save the contents of the query to a temp table and simply export the temp table to csv? Or am i going about this all wrong? Also, is the fact that i'm using Codeigniters Active Record for this prohibitive due to the way that it stores the resultset?

Pseudo Code:

$query = $this->db->select('field1, field2, field3')->where_in('field1',$values_array)->get('tablename');
$data = $this->dbutil->csv_from_result($download_query, $delimiter, $newline); // the some code to save the file
$this->load->helper('download');
force_download($filename, $filedata);

Any advice is welcome! Thank you for reading!

Solution

Ignoring Codeigniter for a moment, you basically have three options for exporting CSV using PHP:

• To disk - typically the slowest option
• To memory - typically the fastest option
• Directly to the browser

In your case I would skip any built-in Codeigniter CSV functions and try streaming directly to the browser (see link above for a complete example).

Answered By - Matt S