Hi,
can anyone explain why a simple UPDATE or DELETE to a Federated table will
send a separate statement for each row?
Consider this example:
create table t2fed (id int not null primary key, x int ) engine=federated
connection='mysql://root:@127.0.0.1:9308/test/t2';
# Column 'x' without a key
select * from t2fed;
+----+------+
| id | x |
+----+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
+----+------+
mysql> delete from t2fed where x = 1;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from t2fed;
+----+------+
| id | x |
+----+------+
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
+----+------+
3 rows in set (0.01 sec)
Everything looks normal. However, on the remote server, the queries
corresponding to a single DELETE are the following:
SHOW TABLE STATUS LIKE 't2'
SELECT `id`, `x` FROM `t2` WHERE ( (`x` = 1) ) AND ( (1=1) )
DELETE FROM `t2` WHERE id = 1 AND x = 1 LIMIT 1
DELETE FROM `t2` WHERE id = 2 AND x = 1 LIMIT 1
DELETE FROM `t2` WHERE id = 3 AND x = 1 LIMIT 1
SHOW TABLE STATUS LIKE 't2'
SELECT `id`, `x` FROM `t2`
Why N statements instead of one?
Why is the LIMIT clause added?
When a UPDATE is issued, and the column in the WHERE clause is indexed, the
result is even worse
# local
drop table t2fed;
create table t2fed (id int not null primary key, x int , key (x))
engine=federated connection='mysql://root:@127.0.0.1:9308/test/t2';
# One query
UPDATE t2fed set x = 200 where x = 2;
#remote: 8 queries
SHOW TABLE STATUS LIKE 't2'
SELECT `id`, `x` FROM `t2` WHERE ( (`x` = 2) ) AND ( (1=1) )
SELECT `id`, `x` FROM `t2` WHERE (`id` = 4)
UPDATE `t2` SET x = 200 WHERE id = 4 AND x = 2
SELECT `id`, `x` FROM `t2` WHERE (`id` = 5)
UPDATE `t2` SET x = 200 WHERE id = 5 AND x = 2
SELECT `id`, `x` FROM `t2` WHERE (`id` = 6)
UPDATE `t2` SET x = 200 WHERE id = 6 AND x = 2
Is this intentional?
Can it be improved? What's the reason for not sending the original query to
the remote server?
Thanks
Best regards
Giuseppe
--
Giuseppe Maxia, QA Developer
MySQL AB, www.mysql.com
