Announcement

Announcement Module
Collapse
No announcement yet.

Slow SELECT ... LIMIT without WHERE clause on federated table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow SELECT ... LIMIT without WHERE clause on federated table

    Hi,

    I've got a problem concerning the federated table engine:

    I created a federated table pointing to a reasonable large remote table (about 800.000 rows, row size 211 Bytes, MyISAM).

    When sending the following query:

    SELECT * FROM TABLE LIMIT 0,30

    the query takes always 9 seconds to complete.

    Trying:

    SELECT * FROM TABLE WHERE primaryKey = 1234

    is fast as usual (< 0.001s).


    I tried tried the federated table on several db servers, always the same result. Now my question is: Does something happen behind the curtain I don't know off? Does Mysql fetch the whole index without a WHERE clause? Is some internal sorting required?

    Anyway, in my opinion the remote db server serving the data should handle this without any delay, shouldn't it?

    Server running the federated engine:
    Mysql 5.1.22
    Debian Linux 2.6.18-5-amd64

    Server serving the data:
    Mysql 4.0.24
    Debian Linux 2.6.8-12-amd64-k8-smp

    Thanks for any help!

  • #2
    Have you tried to use EXPLAIN
    Quote:

    http://dev.mysql.com/doc/refman/5.0/en/explain.html


    or PROFILE
    Quote:

    http://dev.mysql.com/doc/refman/5.0/...-profiles.html


    to see what the query is doing?

    Comment


    • #3
      as expected:


      EXPLAIN SELECT *FROM `table`LIMIT 0 , 30



      id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE table ALL NULL NULL NULL NULL 96



      Mysql only needs to look at 96 rows to retrieve the result but the query takes 9 seconds to complete. Like I said, if I issue the query ON the server (not using the federated table in another db), the query completes in < 0.001s.

      Comment

      Working...
      X