Dear All
I'm running 3 servers with mysql database
- Local : 5.0.18-log, MySQL Community Edition (GPL)
- Main Server : 4.1.22-standard-log, MySQL Community Edition - Standard
(GPL)
- Backup Server : 4.1.20, Source Distribution
I copied a database from the Main Server to Local and Backup Server and
run a SQL
explain command on the database but i get different EXPLAIN result. I
think because
i copied it from one source shouldn't it show the same EXPLAIN result
cause i didn't
change any of the index, keys or records
Here is the sql
SELECT
`t_order_match`.`match_id`,
`t_order_match`.`order_ticket1` as hit_order_ticket,
`tc1`.`user_id` as hit_user_id,
`tc1`.`name` as hit_name,
`tb1`.`company`as hit_company,
`t_order_match`.`order_ticket2` as
queue_order_ticket,
`tc2`.`user_id` as queue_user_id,
`tc2`.`name` as queue_name,
`tb2`.`company` as queue_company,
`t_order_match`.`commodity_id`,
`t_order_match`.`month`,
`t_order_match`.`lot`,
`t_order_match`.`price`,
`t_order_match`.`volume`,
`t_order_match`.`time`
FROM
`t_order_match`
left Join
(`t_order_journal` AS `tj1` Inner Join
(`t_customer` AS `tc1`
INNER JOIN t_brokerage as tb1 ON
tc1.broker_id=tb1.broker_id)
ON `tj1`.`account_id` = `tc1`.`account_id`)
ON `t_order_match`.`order_ticket1` =
`tj1`.`order_ticket`
left Join
(`t_order_journal` AS `tj2`
Inner Join (`t_customer` AS `tc2` INNER JOIN
t_brokerage as tb2
ON tc2.broker_id=tb2.broker_id) ON
`tj2`.`account_id` = `tc2`.`account_id`)
ON `t_order_match`.`order_ticket2` =
`tj2`.`order_ticket`
limit 100;
*The result of the Master *
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------------+-------+-------------+
| 1 | SIMPLE | tc1 | ALL | NULL | NULL
| NULL | NULL | 320 | |
| 1 | SIMPLE | tj1 | index | NULL | acc
| 21 | NULL | 20674 | Using index |
| 1 | SIMPLE | tc2 | *ALL* | NULL | NULL
| NULL | NULL | 320 | |
| 1 | SIMPLE | tj2 | index | NULL | acc
| 21 | NULL | 20674 | Using index |
| 1 | SIMPLE | t_order_match | *ALL * | NULL | NULL
| NULL | NULL | 5972 | |
| 1 | SIMPLE | tb1 | eq_ref | PRIMARY | PRIMARY
| 20 | bbjengine78787-beta.tc1.broker_id | 1 | |
| 1 | SIMPLE | tb2 | eq_ref | PRIMARY | PRIMARY
| 20 | bbjengine78787-beta.tc2.broker_id | 1 | |
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------------+-------+-------------+
7 rows in set (0.00 sec)
*The result of Local *
+----+-------------+---------------+--------+------------------------------+---------+---------+---------------------------------------------+------+-------+
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------------+--------+------------------------------+---------+---------+---------------------------------------------+------+-------+
| 1 | SIMPLE | t_order_match | ALL |
NULL | NULL | NULL |
NULL | 2914 | |
| 1 | SIMPLE | tj1 | eq_ref |
PRIMARY,acc | PRIMARY | 4 |
166bbj-betafull.t_order_match.order_ticket1 | 1 | |
| 1 | SIMPLE | tc1 | ref |
PRIMARY,account_id,broker_id | PRIMARY | 22 |
166bbj-betafull.tj1.account_id | 1 | |
| 1 | SIMPLE | tb1 | ref |
PRIMARY | PRIMARY | 22 |
166bbj-betafull.tc1.broker_id | 1 | |
| 1 | SIMPLE | tj2 | eq_ref |
PRIMARY,acc | PRIMARY | 4 |
166bbj-betafull.t_order_match.order_ticket2 | 1 | |
| 1 | SIMPLE | tc2 | ref |
PRIMARY,account_id,broker_id | PRIMARY | 22 |
166bbj-betafull.tj2.account_id | 1 | |
| 1 | SIMPLE | tb2 | ref |
PRIMARY | PRIMARY | 22 |
166bbj-betafull.tc2.broker_id | 1 | |
+----+-------------+---------------+--------+------------------------------+---------+---------+---------------------------------------------+------+-------+
7 rows in set
*The result of Backup *
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------------+-------+-------------+
| 1 | SIMPLE | tc1 | ALL | NULL | NULL
| NULL | NULL | 320 | |
| 1 | SIMPLE | tc2 | *ALL * | NULL | NULL
| NULL | NULL | 320 | |
| 1 | SIMPLE | t_order_match | *ALL * | NULL | NULL
| NULL | NULL | 5972 | |
| 1 | SIMPLE | tj2 | index | NULL | acc
| 21 | NULL | 20479 | Using index |
| 1 | SIMPLE | tj1 | index | NULL | acc
| 21 | NULL | 20479 | Using index |
| 1 | SIMPLE | tb1 | eq_ref | PRIMARY | PRIMARY
| 20 | bbjengine78787-beta.tc1.broker_id | 1 | |
| 1 | SIMPLE | tb2 | eq_ref | PRIMARY | PRIMARY
| 20 | bbjengine78787-beta.tc2.broker_id | 1 | |
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------------+-------+-------------+
7 rows in set (0.00 sec)
The result from the Local Explain show better cause it just use only one
ALL type while the others have more than one ALL type
is there any explanation about this, cause in real performance,
executing the sql on local is instantenous while on Main and Backup
server they takes very long time
Thanks Before
Chenri
