# Query problem diferent explain

**URL:** <https://forums.percona.com/t/query-problem-diferent-explain/1557>\
**Category:** Other MySQL® Questions\
**Created:** [November 11, 2010, 5:09pm UTC](https://forums.percona.com/t/query-problem-diferent-explain/1557 "2010-11-11T17:09:57Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gjuan](https://avatars.discourse-cdn.com/v4/letter/g/53a042/32.png) [@gjuan](https://forums.percona.com/u/gjuan)\
**Post date:** [November 11, 2010, 5:09pm UTC](https://forums.percona.com/t/query-problem-diferent-explain/1557/1 "2010-11-11T17:09:57Z")

</div>

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\_sobt\_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\_sobt\_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.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [November 14, 2010, 5:07pm UTC](https://forums.percona.com/t/query-problem-diferent-explain/1557/2 "2010-11-14T17:07:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gjuan](https://avatars.discourse-cdn.com/v4/letter/g/53a042/32.png) [@gjuan](https://forums.percona.com/u/gjuan)\
**Post date:** [November 22, 2010, 7:56pm UTC](https://forums.percona.com/t/query-problem-diferent-explain/1557/3 "2010-11-22T19:56:03Z")

</div>

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.