When I click on my "Titles" link to display all of the books in my database, PHP runs out of memory. I'm presented with a blank page and if I view the source the only thing displayed is a solitary "1".

I viewed my Apache error logs and I have one that says PHP ran out of memory while trying to run this script. The first time this happened I had around 7,000 book entries. I checked the memory setting in my "php.ini" and it was set to 128 megs. I thought this should have been enough, but I doubled it to 256 megs which made the problem go away.

I have since added many more books to my calibre database and I now have over 20,000 titles. Needless to say, every time I select to view all of my titles the script will crash with an out-of-memory error.

Is there anyway around this? Clicking on the other links is slow, but eventually they will display their info. I had a different script that would organize the titles into categories from A-Z and it would run fine. I'm thinking that the root of the problem is this one link that has 20,000 entries and if I could somehow break it up into smaller chunks I think it will work again.

The problem arises because the db reads all the books into memory to ease the sorting problem. The various column types need to use different rules for sorting, something that goes beyond what ORDER BY can do.

It wouldn't be hard to "fix" this to use paged accesses when sorting by (for example) title & author, because these have db columns underneath them. Series requires a compound ordering, and dates add even more complexity.

My problem is that I no longer use this server so am not motivated to take on changes of this complexity. I have switched to the calibre content server so I can use it with Calibre Companion (the android app).

If you wish to do the changes, then you would need to change db.php (to page and to sort), the various modules like title.php to pass the paging information to the db routine, and book_base.php to understand that it is being given page segments and not the entire books array.

Another possibility would be to read only the fields required to do the current sort, do the sort, then read the entire records for the desired book ids. This is probably easier than changing to use LIMIT expressions, but won't be nearly as memory efficient.

A third possibility, perhaps the simplest, is that if displaying titles then honor only the sorting options that ORDER BY can do without difficulty. These would be the non-NULL string fields (almost all custom columns can be NULL), and possibly series if you generate the appropriate compound order by for them.