I have a moderate sized database set up and a program that allows users to create ad-hoc queries into the data based on entries in a form, so that I, as the programmer, have control over the actual construction of the queries and can do what is needed to optimize queries. I also keep a log of all queries that are run so that I can easily see the exact query that may have caused a problem.
For the most part, unless a query is quite complex, there is no problem with the response time - from a few seconds up to a minute or two for more complex queries or one returning very large result sets. Recently a seemingly very simple query has resulted in unacceptably long processing times. After playing around with the query in PhpMyAdmin I am totally perplexed as to what could be causing the problem. Sometimes the query will execute in less than 30 seconds, but other times it takes from 4 to 10 or more minutes. It never seems to complete in between 30 seconds and 4 minutes. To try to isolate the problem today I did a lot of testing on an isolated server - nothing on it but MySql and this database and no one but me has access to it. Tried rearranging the joins and eliminating one of the joins as well as everything else I could think of to figure out what could be causing the issue. Through all of the testing I got consistent results in the four minute range for all of the variations I tried - repeated attempts with the same query varied by only a second or two. Then I want back to my program and ran the original query on the "public" database - the same place that the problem had been originally found and instead of timing out the gateway (five minute limit) as it had done consistently over the past few days it ran it successfully in about 20 seconds. I was able to repeat this many times both using the program and by entrering the query into PhpMyAdmin. Still takes 4 minutes on the private server, though. A couple of hours later - shortly before starting this message - I tried again on the public server and again the response time was under 30 seconds. Trying again now and it's on its way to timing out again. Checked and there are no other processes running on the server - volume is usually low as there are less than 100 users total. Any ideas of what could be causing the varied response time on a simple query when everything on the server appears to be identical from one run to another? Are there settings that can be made on the server to tweak response time for a database/query like this? Here are stats on the files involved in the query and the actual query I am trying to run. Note that the number of receipts with amount >= 10000 is very smal compared to the total number of records. Main: 900,000 records, 500 Mb (886,361 where recordtype = "INDIVIDUAL") Primary key: id (int) Receipt: 4,500,000 records, 700 Mb (6,817 where amount <= 10000) Primary key: id (int) Indexed on: mainid (int) Committee: 4,500 records, 600Kb (1,476 where party = "D") Primary key: id (int) Indexed on: Comm_id (varchar(6)) create temporary table tmp type = heap select distinct 3 filterid, m.id, "" GroupLevel, 0 GroupCum from main m left join receipt r on m.id = r.mainid left join campaccommon.committee c on r.comm_id = c.Comm_id where recordtype = "INDIVIDUAL" and c.party = "D" and r.amount >= 10000 Returns 294 records. Thanks for any insight you can offer. - Leo Siefert
