Faster MySQL Queries?

Question

At my work I have several tables with over 200,000 rows of data. I have to set-up some queries that look over 15,000+ at a time so sometimes I get this error:

PHP Fatal error: Maximum execution time of 180 seconds exceeded

So, how do I speed up faster queries?

The query is like this:

SELECT  toemail, toname
FROM    email_sent
WHERE   companyid = '$member[companyid]'

Thanks.

Answer 1

Adding an index if you haven't already. Another way is to switch from MyISAM to InnoDB.

Answer 2

The first thing you might want to look into is indexing any columns which participate in the query. For example, if you your query is always testing the value of a column FirstName, you might want to index that.

If you provide a DDL (Data Definition Lanaguage) script or a description of the tables, as well as the queries that are taking so long, we might be able to provide better tips for indexing.

If you've already tuned as much as you can and you still get timeouts, you might want to see if you can increase the transaction timeout limit. I don't know enough about your server setup to give details, but that sort of thing is usually possible.

UPDATE

If your query is:

SELECT toemail,toname FROM email_sent WHERE companyid  = '$member[companyid]' 

My first question is: do you have an index on companyid and if not, does creating one improve performance?

Answer 3

Create an index on email_sent (company_id):

CREATE INDEX ix_emailsent_companyid ON email_sent (company_id)

Answer 4

Optimization might be the answer. If it's not enough, you can always just increase PHP's time limit.

This will set it for just that script:

set_time_limit docs

Set the number of seconds a script is allowed to run. If this is reached, the script returns a fatal error. The default limit is 30 seconds or, if it exists, the max_execution_time value defined in the php.ini.

Or, edit php.ini and change the max_execution_time setting. This will change it globally, of course. It sounds like it has already been adjusted (by your sysadmin?) as the default is 30 seconds.