Optimizing memory usage of a Zend database

[cmiddlebrook]

I have a database driven application that runs jobs from a queue that is held in a MySql database and accessed via Zend_DB. I have an engine that is run via a cron job that picks up these jobs and processes them. My problem is that each instance of this engine takes up around 18Mb of memory.

 

I have a feeling the problem is with the following query:

 

"SELECT * FROM `my queue table` WHERE `the date field` < '$now' ORDER BY `the date field` ASC LIMIT 0, 1"

 

This is looking for the next job from the queue to process. I was hoping that by having the LIMIT clause on the end of the query that only one record would be fetched and that would keep the memory down but I'm wondering if it is the select itself that is causing the problem. In order to do the ORDER BY clause, it has to presumably sort through all the records in the queue - now over 100,000 of them and growing. Could this be the problem?

 

I could refactor the way the queue us handled but that would be a bit of work, so is there a way I can actually test how much memory a particular query uses so I can be sure where my problem lies as right now I'm just guessing.

 

Caroline