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
