There are LOTS of things that could have cause this.
First, it cannot use the index because of the way the WHERE is written.
Suggest changing to
WHERE timeA <= 1266143632
MyISAM or InnoDB?
How much RAM? (on each machine)
SHOW VARIABLES LIKE '%buffer'; -- on each machine!
Run the query twice -- it may run faster the second time. (10x faster
is my rule of thumb for cold system versus second run)
What do you mean by "static"?
That one query would be even faster this way (mathematically equivalent)
SELECT *, ABS(timeA-1266143632) FROM tableA WHERE timeA <= 1266143632
ORDER BY *timeA DESC* LIMIT 1;
That should take more like 0.01 sec.-- on any machine! (OK, a cold
machine might take more like 0.05s.)
On 4/29/10 12:50 AM, Johan De Meersman wrote:
> I'm sure I answered to this exact post yesterday :-)
> On Wed, Apr 28, 2010 at 11:19 PM, Kandy Wong<kandyw@stripped> 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
>> 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.
>> But if I dump the whole database to another machine as static, it only
>> takes 3.70 sec or even less to finish.
>> The table has 386 columns and timeA is an index.
>> Is there a way to improve the query or any other factors that would
>> affect the performance?
>> MySQL Replication Mailing List
>> For list archives: http://lists.mysql.com/replication
>> To unsubscribe: