Timeout on Large MySQL Query

Question

I have this code:

$theQuery = mysql_query("SELECT phrase, date from wordList WHERE group='nouns'");
while($getWords=mysql_fetch_array($theQuery)) {
 echo "$getWords[phrase] created on $getWords[date]<br>";
}

The query has 75,000 results, and every time I run the code I get an error.

Answer 1

As a guess I'd say your timing out on the MySQL query rather than echoing out the results (from your title)

Have you tried setting an index on the group column on your MySQL table? This will slow your inserts should make a huge difference on the select.

See http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Answer 2

Several issues could be at play here, all of which are due to settings in your php.ini. Your script could be timing out since PHP defaults to a 30 second maximum for script execution. The other reason (and just as likely) is that you're hitting a script memory limit which is defaulted to 8MB per script execution.

Open php.ini and search for "Resource Limits" and make the appropriate modifications.

Answer 3

php.ini is a good first step but you might find yourself on a server where you can't change those settings to something that will work. In that case it might make sense to break up what you're doing in chunks.

For example, if this is for output, you could run 25,000 results at a time using LIMIT. Stick the output in a file and then just use the file for your output. You can update your file once a night/hour/whatever with a cron job if you need the output to stay fresh.