Query problem diferent explain

MySQL & MariaDB Other MySQL® Questions
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_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.

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.

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.