Memory for large data in PDO

Question

PDO seems like a little tricky, but I have a large database and I'm getting this error,

Fatal error: Allowed memory size of 100663296 bytes exhausted (tried to allocate 256 bytes)

When using,

fetchAll()

What is the best way to solve it?

Answer 1

You can temporarily allocate extra memory using:

ini_set('memory_limit', '750M');

The actual problem is PHP itself not PDO per-se... the PHP Array it's creating is too big... try above... obviously 750M is a lot but ya get the idea!

You will probably fine in php.ini memory_limit is 128M by default ... resulting in the error you're getting when the script execution over all exceeds this.

ALTERNATIVELY:

(as per my comments) - Only fetch what you can display for the user, i.e. implement some form of Paging.

A great combination is using jQuery Datatables and server-side pipeline of data. I.e. it will fetch from DB for each page as requested.

ALSO:

Make sure your schema has been properly designed and normalised and not just got 100's of columns!