List:Internals« Previous MessageNext Message »
From:Giuseppe Maxia Date:June 19 2007 9:08am
Subject:Rationale behind FEDERATED engine queries
View as plain text  
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
Thread
Rationale behind FEDERATED engine queriesGiuseppe Maxia19 Jun
  • Re: Rationale behind FEDERATED engine queriesPeter B. Volk19 Jun
    • Re: Rationale behind FEDERATED engine queriesJay Pipes19 Jun
      • Re: Rationale behind FEDERATED engine queriesBaron Schwartz19 Jun
        • Re: Rationale behind FEDERATED engine queriesPeter B. Volk19 Jun
          • Re: Rationale behind FEDERATED engine queriesEric Prud'hommeaux19 Jun
  • Re: Rationale behind FEDERATED engine queriesPhilip Stoev23 Jun