•  

Announcement

Announcement Module
Collapse
No announcement yet.

Query problem diferent explain

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

  • Query problem diferent explain

    #1
    Hi,

    I have a database in two diferent servers. I execute a process in the both server and in server1 it is taken 2 hours and in server2 is taken 8 hours.

    I was testing the select and i found that the explain is diferent in each server.

    The query is:

    SELECT products.seob_id sob_id,
    products.seob_id sob_owner,
    products.seob_content,
    products.sobt_id,
    products.seob_owner_sobt_id owner_sobt_id
    FROM products,
    products_rel
    WHERE products_rel.sobt_a_id = products.seob_id
    AND products.seob_id = products_rel.sobt_b_id
    AND products.sein_id = 1435217
    ORDER BY products.seob_id;

    Server1 Explain

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE products ref PRIMARY,seob_sein_pk_ix seob_sein_pk_ix 4 const 89 Using where
    1 SIMPLE products_rel ref sobr_sobt_a_fk_ix,sobr_sobt_b_fk_ix,sobr_sobt_b_so bt_a_fk_ix sobr_sobt_b_sobt_a_fk_ix 9 test.products.seob_id,test.products.seob_id 1 Using where; Using index


    Server2 Explain

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE products ref PRIMARY,seob_sein_pk_ix seob_sein_pk_ix 4 const 89 Using where; Using filesort
    1 SIMPLE products_rel ref sobr_sobt_a_fk_ix,sobr_sobt_b_fk_ix,sobr_sobt_b_so bt_a_fk_ix sobr_sobt_a_fk_ix 4 test.products.seob_id 1 Using where


    Why the second explain in server 2 is not using the same index in table products_rel? Why in extra is using filesort?


    The tables structure are:

    CREATE TABLE `test`.`products` (
    `seob_id` int(9) NOT NULL auto_increment,
    `seob_content` varchar(2048) default NULL,
    `seob_owner_sobt_id` int(9) default NULL,
    `seob_create` datetime default NULL,
    `seob_priority` int(9) default NULL,
    `sein_id` int(9) NOT NULL,
    `sobt_id` int(9) NOT NULL,
    PRIMARY KEY (`seob_id`),
    KEY `seob_sein_pk_ix` (`sein_id`),
    KEY `seob_sobt_pk_ix` (`sobt_id`),
    KEY `products_ix_02` (`seob_owner_sobt_id`),
    ) ENGINE=InnoDB AUTO_INCREMENT=227375118 DEFAULT CHARSET=latin1;


    CREATE TABLE `test`.`products_rel` (
    `sobr_id` int(9) NOT NULL auto_increment,
    `sobt_a_id` int(9) NOT NULL,
    `sobt_b_id` int(9) default NULL,
    PRIMARY KEY (`sobr_id`),
    KEY `sobr_sobt_a_fk_ix` (`sobt_a_id`),
    KEY `sobr_sobt_b_fk_ix` (`sobt_b_id`),
    KEY `sobr_sobt_b_sobt_a_fk_ix` (`sobt_a_id`,`sobt_b_id`),
    CONSTRAINT `sobr_sobt_a_id_fk` FOREIGN KEY (`sobt_a_id`) REFERENCES `products` (`seob_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `sobr_sobt_b_id_fk` FOREIGN KEY (`sobt_b_id`) REFERENCES `products` (`seob_id`) ON DELETE CASCADE ON UPDATE NO ACTION
    ) ENGINE=InnoDB AUTO_INCREMENT=227375118 DEFAULT CHARSET=latin1;


    Server1 Mysql version:5.0.77-log
    Server2 Mysql version:5.0.45-log


    Thanks for your help.
    Tags: None

  • #2
    I don't believe the execution plan I see on server 1; it cannot perform this query without 'using filesort' when it uses the index seob_sein_pk_ix. Did you run explain with 'order by'?


    For the current query, try this:
    Remove the index KEY `sobr_sobt_a_fk_ix` (`sobt_a_id`) as it is contained in the other index.


    Change the index KEY `seob_sein_pk_ix` (`sein_id`), to an index on the columns (sein_id, seob_id).

    And let me know the new execution time.

    Comment


    • #3
      Thanks gmouse for your answer. The problem was solved. The bad perfomance was detected because sort_buffer_size had a high value. So i reduced it to the default value and now the process take the time estimated.

      I am going to apply your recomendations to verify how the execution plan change.

      Thanks again for your help.

      Comment

      Working...
      X