# MySQL Replication **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 5, 2007, 12:02am UTC](https://forums.percona.com/t/mysql-replication/226 "2007-03-05T00:02:17Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![LinuxFreak](https://avatars.discourse-cdn.com/v4/letter/l/a9adbd/32.png) [@LinuxFreak](https://forums.percona.com/u/LinuxFreak)\ **Post date:** [March 5, 2007, 12:02am UTC](https://forums.percona.com/t/mysql-replication/226/1 "2007-03-05T00:02:17Z")
Dear Peter, Hello, I am working for a company which have huge traffic on their website. They have huge Database and using MySQL Replication. REPLICATION MYSQL SERVER A REPLICATION MYSQL SERVER B REPLICATION MYSQL SERVER C ----\> WEBSITE \<—\> MASTER MYSQL SERVER REPLICATION MYSQL SERVER D REPLICATION MYSQL SERVER E Now, ALL SELECT’s statements executed from SERVER A,B,C,D,E and ALL INSERT,DELETE,UPDATE,CREATE,DROP’s statements executed from MASTER MYSQL SERVER. The problem is there will be very high load on SERVER A,B,C,D,E. Can you please help me with that. Please ask me if you need any info. Best Regards. ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [March 5, 2007, 3:46am UTC](https://forums.percona.com/t/mysql-replication/226/2 "2007-03-05T03:46:37Z")
I’m not sure what your question has to do with MySQL Replication. you have high load on your severs, I assume from MySQL you need to check MySQL settings and most important queries you’re running to see if you can optimize them. Check EXPLAIN statement for the queries to start with. ---
**Author:** ![LinuxFreak](https://avatars.discourse-cdn.com/v4/letter/l/a9adbd/32.png) [@LinuxFreak](https://forums.percona.com/u/LinuxFreak)\ **Post date:** [March 5, 2007, 5:37am UTC](https://forums.percona.com/t/mysql-replication/226/3 "2007-03-05T05:37:58Z")
Dear Peter, Hello, This is an example query which took 13.48 Seconds to EXECUTE and when there 40 to 50 queries then server load spikes to 120. We have Dual Xeon 3.6 GHz with 4 GB of RAM on this replication node. mysql\> EXPLAIN SELECT keyword AS title, rank, ondate → FROM (SELECT \* FROM seo\_keyword\_report → WHERE (rank /10) \>1 → ORDER BY ondate DESC → )keywords → GROUP BY keyword → ORDER BY rank ASC → limit 4,0; ±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±-------±------------- -------------------+ | id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra | ±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±-------±------------- -------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 233308 | Using temporary; Using filesort | | 2 | DERIVED | seo\_keyword\_report | ALL | NULL | NULL | NULL | NULL | 389861 | Using filesort | ±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±-------±------------- -------------------+ 2 rows in set (13.48 sec) Best Regards. ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [March 5, 2007, 5:46am UTC](https://forums.percona.com/t/mysql-replication/226/4 "2007-03-05T05:46:38Z")
Forgot to mention. The query is also designed wrong. Why would you sort in derived table if you sort again in external query - derived tables do not need order by (unless you use limit) ---
**Author:** ![LinuxFreak](https://avatars.discourse-cdn.com/v4/letter/l/a9adbd/32.png) [@LinuxFreak](https://forums.percona.com/u/LinuxFreak)\ **Post date:** [March 5, 2007, 5:53am UTC](https://forums.percona.com/t/mysql-replication/226/5 "2007-03-05T05:53:17Z")
Dear Peter, Hello, Can you please give me an better example of above query which i have mentioned. Best Regards. ---
**Author:** ![LinuxFreak](https://avatars.discourse-cdn.com/v4/letter/l/a9adbd/32.png) [@LinuxFreak](https://forums.percona.com/u/LinuxFreak)\ **Post date:** [March 5, 2007, 7:54am UTC](https://forums.percona.com/t/mysql-replication/226/6 "2007-03-05T07:54:28Z")
Dear Peter, Hello, I am pasting you describe of my tables; mysql\> describe seo\_keyword\_report; ±-----------------±-----------------±-----±----±------- -------------±---------------+ | Field | Type | Null | Key | Default | Extra | ±-----------------±-----------------±-----±----±------- -------------±---------------+ | id | int(11) | | PRI | NULL | auto\_increment | | keyword | varchar(255) | | MUL | | | | search\_engine | varchar(255) | | | | | | rank | int(11) | YES | | NULL | | | url | tinytext | | | | | | competetion | int(10) unsigned | | | 0 | | | ondate | timestamp | YES | | 0000-00-00 00:00:00 | | ±-----------------±-----------------±-----±----±------- -------------±---------------+ 7 rows in set (0.00 sec) and indexes on this table mysql\> show index from seo\_keyword\_report; ±----------------------------±-----------±-----------±-- -----------±------------±----------±------------±------- --±-------±-----±-----------±--------+ | Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | ±----------------------------±-----------±-----------±-- -----------±------------±----------±------------±------- --±-------±-----±-----------±--------+ | seo\_keyword\_report | 0 | PRIMARY | 1 | id | A | 389883 | NULL | NULL | | BTREE | | | seo\_keyword\_report | 1 | keyword | 1 | keyword | A | 129961 | NULL | NULL | | BTREE | | ±----------------------------±-----------±-----------±-- -----------±------------±----------±------------±------- --±-------±-----±-----------±--------+ 2 rows in set (0.00 sec) The problem is when i removed ORDER BY ondate DES and ORDER BY rank ASC then this query will return results in seconds but we need latest date result for which we need to use ORDER BY ondate DES and also we need rank in ascending order so we have to use this ORDER BY rank ASC Best Regards. ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [March 5, 2007, 8:01am UTC](https://forums.percona.com/t/mysql-replication/226/7 "2007-03-05T08:01:18Z")
Well Change order by to sort by rank,date when or whatever. But again as I mentioned you need to normalize your data for best speed. ---
**Author:** ![LinuxFreak](https://avatars.discourse-cdn.com/v4/letter/l/a9adbd/32.png) [@LinuxFreak](https://forums.percona.com/u/LinuxFreak)\ **Post date:** [March 5, 2007, 8:12am UTC](https://forums.percona.com/t/mysql-replication/226/8 "2007-03-05T08:12:08Z")
Dear Peter, Hello, I used this query manually but its giving me an error. mysql\> select title AS keyword, rank, ondate from seo\_keyword\_report where rank\>10 sort by rank,date; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘sort by rank,date’ at line 1 Best Regards. ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [March 5, 2007, 8:27am UTC](https://forums.percona.com/t/mysql-replication/226/9 "2007-03-05T08:27:02Z")
order by rank.