sql - MySQL Query Optimization for large tables -

i have query take 50 seconds

select `security_tasks`.`itemid` `itemid` `security_tasks` inner join `relations` on (`relations`.`user_id` = `security_tasks`.`user_id` ,    `relations`.`relation_type_id` = `security_tasks`.`relation_type_id` , `relations`.`relation_with` = 3001 )   

records in security_tasks = 841321 || records in relations = 234254

create table `security_tasks` (   `id` int(11) not null auto_increment,   `user_id` int(11) default null,   `itemid` int(11) default null,   `relation_type_id` int(11) default null,   `task_id` int(2) default '0',   `job_id` int(2) default '0',   `task_type_id` int(2) default '0',   `name` int(2) default '0'   primary key (`id`),   key `itemid` (`itemid`),   key `relation_type_id` (`relation_type_id`),   key `user_id` (`user_id`) ) engine=innodb auto_increment=1822995 default charset=utf8;  create table `relations` (   `id` int(11) not null auto_increment,   `user_id` int(11) default null,   `relation_with` int(11) default null,   `relation_type_id` int(11) default null,   `manager_level` int(11) default null,   primary key (`id`),   key `user_id` (`user_id`),   key `relation_with` (`relation_with`),   key `relation_type_id` (`relation_type_id`) ) engine=innodb auto_increment=1082882 default charset=utf8; 

what can make fast, 1 or 2 seconds fast

explain :

id select_type table   type    possible_keys   key key_len ref rows    1   simple  relations   ref user_id,relation_with,relation_type_id  relation_with   5   const   169 using 1   simple  security_tasks  ref relation_type_id,user_id    user_id 5   transparent.relations.user_id   569 using where

update :

adding composite key minimized time 20 seconds

alter table security_tasks add index (user_id, relation_type_id) ; alter table relations add index (user_id, relation_type_id) ; alter table relations add index (relation_with) ;

the problem when relations table has large data selected user (relations.relation_with` = 3001 )

any ideas ?

adjust compound index slightly, don't two, 3 parts

alter table relations add index (user_id, relation_type_id, relation_with)

the index not have on joined columns, should based on joined columns plus else makes sense querying criteria concerned (within reason, takes time learn more efficiencies). so, in case suggested, know join on user , type, specific relation with... added same index.

additionally, security task table, add itemid index make covering index (ie: covers join conditions , data element(s) want retrieve). technique, , should not include other elements in query, since single column might make sense scenario. so, "covering indexes", in essence, covering index qualifies join, since has "itemid", engine not have go raw data pages of entire security tasks table 1 column. it's part of index grabs whatever qualified join , comes along ride , done.

alter table security_tasks add index (user_id, relation_type_id, itemid) ;

and readability purposes, long table names, it's use aliases

select        st.itemid           security_tasks st          inner join relations r             on st.user_id = r.user_id              , st.relation_type_id = r.relation_type_id             , r.relation_with = 3001