Databases
StackOverflow2013
Postsdbo
POSpeed up csv export when using php from mysql database query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpeed up csv export when using php from mysql database query
    primarykey
    data
    text
    <p>Ok, so i've got a web system (built on codeigniter &amp; 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.</p> <p>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.</p> <p>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?</p> <p>Pseudo Code:</p> <pre><code>$query = $this-&gt;db-&gt;select('field1, field2, field3')-&gt;where_in('field1',$values_array)-&gt;get('tablename'); $data = $this-&gt;dbutil-&gt;csv_from_result($download_query, $delimiter, $newline); // the some code to save the file $this-&gt;load-&gt;helper('download'); force_download($filename, $filedata); </code></pre> <p>Any advice is welcome! Thank you for reading!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohn
    1. USJohn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSpeed up csv export when using php from mysql database query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSpeed up csv export when using php from mysql database query
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSpeed up csv export when using php from mysql database query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCould you provide a pseudo-code example of the program flow of your export process?
      singulars
      1. POSpeed up csv export when using php from mysql database query
      1. USCrazyWebDeveloper
    2. COYes of course! Essentially it's: $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); I cant get the code to style properly! Sorry!
      singulars
      1. POSpeed up csv export when using php from mysql database query
      1. USJohn
    3. CO@John put the code in the original post.
      singulars
      1. POSpeed up csv export when using php from mysql database query
      1. USmanuerumx
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload