MySQL slowness on first query


#1

I have a table with thousands of records.

If I execute a query to grab the first 100 records, the query will take a lot of time to complete (minutes) the first time it’s executed.

If I execute this same query a few more times, it will end up taking “0.00 sec” to complete.

I used to think that it’s because MySQL is crappy and unreliable for larger tables, but now I found that these queries always execute at lightning speed on my modest Windows desktop (on even larger tables).

Have any of you experienced such problems? Do you know the cause?

Thanks


#2

PS: I forgot to note that if I wait for a while (e.g. 30 minutes), the queries will start all over from slow to fast.


#3

Make sure your table is indexed and check with dreamhost support for hints on making your queries count. They spend a lot of time doing this to help everyone.

Regarding questions of scalability… So, are you saying that your site is bigger than slashdot? (Slashdot uses mysql.)

Wholly


#4

As far as I know, but don’t take my word for it, I don’t know for sure, the MySQL server will cache queries, so if you send the same query, and the result set would be identical, MySQL catches this and will return the result set from the cache instead of looking it up again each time you request it. It would make sense then to see the server redo the query every 30 min, which is probably the time to live for the cache. To see if this is the case, update one of your tables between queries and see if there is a difference.

I agree with the previous poster that MySQL is extremely robust and can handle even high-traffic, high-volume sites like Slashdot (for example by caching repeat queries…). I’ve seen similar issues with my database connections on Dreamhost, however. It appears that sometimes my database server comes under heavy load and takes a long time to fulfill my queries, not minutes, usually it’s a matter of seconds, but it can be up to 45 seconds sometimes for the page to finish loading, even though the table only has a few hundred records.


Patrick
http://inmyholyopinion.com


#5

As wholly mentioned, make sure you’ve created indexes on the columns that you’re using to limit your result set. I’ve got a MySQL database for zipcodes that had a varchar(5) field for the zipcode, and 4 double precision floats for latitude, longitude, and both of those values in radian measure. A direct lookup of 1 record, keyed against the unindexed text column containing zipcode would take about 0.5 seconds with 35000 records. I duplicated the zipcode values as zero-padded, unsigned, medium integers which I indexed, and now the lookup of a single record takes about 0.04 seconds.

As you’ve noticed any decent RDBMS, and MySQL is indeed enterprise strength, will cache queries for faster execution. The first time they’re slower because the database is creating an execution plan, in an attempt to find the quickest method to perform your query. Seemingly insignificant things can have a huge impact on performance here. I recently was running a fairly simple query in Oracle, arguably enterprise strength, that returned about 800 rows from a table containing about 1 million records. The query was taking about 45 seconds. We looked at it in the query analyzer and found some full table scans. After some tweaking we got the query down to about 2 seconds. Morale: don’t blame the database until you do a little experimenting.

Another thing to pay attention to is how you form your select query. Using something like “SELECT *” forces the db to look up all values in a systems object table. Explicitly listing the actual columns you want can improve performance.

Maybe if you post the offending query someone can offer a little advice.


#6

Indeed, it seems to have been related to indexes.

My queries were of the type:

select * from table where col1 = val1 and col2 = val2 order by col3, col4;

I found that a rule of thumb for the above case is to add 2 indexes like:

(col1, col3, col4)
and
(col2, col3, col4)

I already had plenty of indexes (but not on all columns used - like above - by my queries), but the fact that the MySQL installed on my Desktop executed the queries with lightning speed, made me think that the problem might have been on DreamHost’s end, probably a weird limitation - which may still be true, but probably this limitation shouldn’t have much impact on well constructed queries and tables.


#7

The DB servers are shared too so other users may have swapped your in memory indexes out.

Generally DB servers are really good today even without composite indexes. As long as the field is indexed, you’ll avoid a complete table scan. (arguably the most expensive action a DB can make short of a Cartesian query.)

Don’t be afraid to ask DH support for hints. They work in this DB environment all the time.

Wholly