mysql - Subquery processing more rows than necessary -

i optimising queries , found can't head around.

i using following query select bunch of categories, combining them alias table containing old , new aliases categories:

select `c`.`id` `category.id`,      (select `alias`     `aliases`     category_id = c.id     , `old` = 0     , `lang_id` = 1     order `id` desc     limit 1) `category.alias` (`categories` c) `c`.`status` =  1 , `c`.`parent_id` =  '11'; 

there 2 categories value of 11 parent_id, should 2 categories alias table.

still if use explain says has process 48 rows. alias table contains 1 entry per category (in case, can more). indexed , if understand correctly therefore should find correct alias immediately.

now here's weird thing. when don't compare aliases categories conditions, manually category ids query returns, process 1 row, intended index.

so replace where category_id = c.id where category_id in (37, 43) , query gets faster:

the thing can think of subquery isn't run on results query before filtering done. kind of explanation or welcome!

edit: silly me, where in doesn't work doesn't make unique selection. question still stands though!

create table schema

create table `aliases` (     `id` int(10) unsigned not null auto_increment,     `lang_id` int(2) unsigned not null default '1',     `alias` varchar(255) default null,     `product_id` int(10) unsigned default null,     `category_id` int(10) unsigned default null,     `brand_id` int(10) unsigned default null,     `page_id` int(10) unsigned default null,     `campaign_id` int(10) unsigned default null,     `old` tinyint(1) unsigned default '0',     primary key (`id`),     key `product_id` (`product_id`),     key `category_id` (`category_id`),     key `page_id` (`page_id`),     key `alias_product_id` (`product_id`,`alias`),     key `alias_category_id` (`category_id`,`alias`),     key `alias_page_id` (`page_id`,`alias`),     key `alias_brand_id` (`brand_id`,`alias`),     key `alias_product_id_old` (`alias`,`product_id`,`old`),     key `alias_category_id_old` (`alias`,`category_id`,`old`),     key `alias_brand_id_old` (`alias`,`brand_id`,`old`),     key `alias_page_id_old` (`alias`,`page_id`,`old`),     key `lang_brand_old` (`lang_id`,`brand_id`,`old`),     key `id_category_id_lang_id_old` (`lang_id`,`old`,`id`,`category_id`) ) engine=innodb auto_increment=112392 default charset=utf8 row_format=compact; 

select ...     x=1 , y=2     order id desc     limit 1 

will performed in 1 of several ways.

since have not shown indexes have (show create table), cover cases...

• index(x, y, id) -- can find last row condition, not need @ more 1 row.
• some other index, or no index: scan descending last id checking each row x=1 , y=2, stopping when (if) such row found.
• some other index, or no index: scan entire table, checking each row x=1 , y=2; collect them temp table; sort id; deliver 1 row.

some of explain clues:

• using -- not much
• using filesort -- did sort, apparently order by. (it may have been entirely done in ram; ignore 'file'.)
• using index condition (not "using index") -- indicates internal optimization in can check where clause more efficiently used in older versions.

do not trust "rows" in explain. reasonably correct, off orders of magnitude. here better way see "how work" being done in rather fast query:

flush status; select ...; show session status 'handler%'; 

with create table, may have suggestions on how improve index.