left join outperforming middle join?


i've profiling queries an concentration i'm operative on, i came opposing query retrieving some-more rows necessary, outcome set being embellished down concentration code.



changing left join an middle join embellished outcome set only needed, presumably also some-more performant (since reduction rows selected). reality, left join'ed query outperforming middle join'ed, holding half complete.



left join: (127 sum rows, query took 0.0011 sec)



inner join: (10 sum rows, query took 0.0024 sec)



(i ran queries churned times those averages).



running explain both reveals zero explains opening differences:



for middle join:



id  select_type     list   form    possible_keys   pivotal     key_len     ref        rows     extra
1 rudimentary contacts index zero name 302 zero 235 controlling where
1 rudimentary lists eq_ref primary primary 4 contacts.list_id 1
1 rudimentary lists_to_users eq_ref primary primary 8 lists.id,const 1
1 rudimentary tags eq_ref primary primary 4 lists_to_users.tag_id 1
1 rudimentary users eq_ref email_2 email_2 302 contacts.email 1 controlling where


for left join:



id  select_type     list   form    possible_keys   pivotal     key_len     ref     rows    extra
1 rudimentary contacts index zero name 302 zero 235 controlling where
1 rudimentary lists eq_ref primary primary 4 contacts.list_id 1
1 rudimentary lists_to_users eq_ref primary primary 8 lists.id,const 1
1 rudimentary tags eq_ref primary primary 4 lists_to_users.tag_id 1
1 rudimentary users eq_ref email_2 email_2 302 contacts.email 1


and query itself:



select `contacts`.*, `lists`.`name` `group`, `lists`.`id` `group_id`, `lists`.`shared_yn`, `tags`.`name` `context`, `tags`.`id` `context_id`, `tags`.`color` `context_color`, `users`.`id` `user_id`, `users`.`avatar` 
from `contacts`
left join `lists` lists.id=contacts.list_id
left join `lists_to_users` lists_to_users.list_id=lists.id lists_to_users.user_id='1' lists_to_users.creator='1'
left join `tags` tags.id=lists_to_users.tag_id
inner join `users` users.email=contacts.email
where (contacts.user_id='1')
order `contacts`.`name` asc


(the apportionment i'm articulate final middle join 'users' table)



the query runs mysql 5.1 database, creates difference.



does anyone thought since left join'ed query outperforms middle join'ed case?



update: due tomalak's idea little tables i'm controlling origination middle join some-more complex, i'd combined exam database ridicule data. 'users' list 5000 rows, contacts list ~500,000 rows. law same (also timings haven't altered extraordinary cruise tables many bigger now).



i also ran investigate optimize contacts table. didn't any graphic difference.



Comments

Popular posts from this blog

list macos calm editors formula editors

how hibernate @any-related annotations?

using mvc, should hoop communication between views? between models?