On Wed, Apr 28, 2010 at 12:17 AM, Kandy Wong <kan...@triumf.ca> wrote:
> Is it true that the performance of running a query on a live replication > master and slave has to be much slower than running a query on a static > server? > > I've tried to run the following query on a replication master and it takes > 1 min 13.76 sec to finish. > SELECT *, ABS(timeA-1266143632) as distance FROM tableA WHERE timeA - > 1266143632 <= 0 ORDER BY distance LIMIT 1; > > And if I run it on the replication slave, it takes 24.15 sec. > The slave most likely didn't have the table data in memory, so had to do a disk read. Also, if you've performed that query before on your master, it may still have been in the query cache. Add the SQL_NO_CACHE keyword right after the word "select" to disable the query cache when checking query performance. > But if I dump the whole database to another machine as static, it only > takes 3.70 sec or even less to finish. > When you load just that table, the data you just inserted will still have been in memory and/or in the OS cache, avoiding a disk read. > Is there a way to improve the query or any other factors that would affect > the performance? > Rewrite your where clause to "where timeA <= 1266143632" - that will allow you to put an index on timeA and benefiting from it. Also, ordering by timeA should give the same ordering as by distance, and may also use the index to sort instead of a filesort. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
