Databases
StackOverflow2013
Postsdbo
POPDO/MySQL memory consumption with large result set
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPDO/MySQL memory consumption with large result set
    primarykey
    data
    text
    <p>I'm having a strange time dealing with selecting from a table with about 30,000 rows.</p> <p>It seems my script is using an outrageous amount of memory for what is a simple, forward only walk over a query result.</p> <p>Please note that this example is a somewhat contrived, absolute bare minimum example which bears very little resemblance to the real code and it cannot be replaced with a simple database aggregation. It is intended to illustrate the point that each row does not need to be retained on each iteration.</p> <pre><code>&lt;?php $pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array( PDO::ATTR_ERRMODE=&gt;PDO::ERRMODE_EXCEPTION, )); $stmt = $pdo-&gt;prepare('SELECT * FROM round'); $stmt-&gt;execute(); function do_stuff($row) {} $c = 0; while ($row = $stmt-&gt;fetch()) { // do something with the object that doesn't involve keeping // it around and can't be done in SQL do_stuff($row); $row = null; ++$c; } var_dump($c); var_dump(memory_get_usage()); var_dump(memory_get_peak_usage()); </code></pre> <p>This outputs:</p> <pre><code>int(39508) int(43005064) int(43018120) </code></pre> <p>I don't understand why 40 meg of memory is used when hardly any data needs to be held at any one time. I have already worked out I can reduce the memory by a factor of about 6 by replacing "SELECT *" with "SELECT home, away", however I consider even this usage to be insanely high and the table is only going to get bigger.</p> <p>Is there a setting I'm missing, or is there some limitation in PDO that I should be aware of? I'm happy to get rid of PDO in favour of mysqli if it can not support this, so if that's my only option, how would I perform this using mysqli instead?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShabbyrobe
    1. USShabbyrobe
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPDO/MySQL memory consumption with large result set
      1. USShabbyrobe
      1. VTBountyStart
    2. VO
      singulars
      1. POPDO/MySQL memory consumption with large result set
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POPDO/MySQL memory consumption with large result set
      1. USErik
      1. VTFavorite
    1. This table or related slice is empty.
 

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