Complex query takes 30 seconds with no full table scan

I'm running MySQL 5.1 on a 32 bit machine, I'm using Hibernate criteria to generate the query below; for some reason I don't understand it takes 30 seconds to execute even though according to what 'explain' says, no full table scan is being performed.

In full detail we have a stocks, stocks_groups, indicators, indicator_definition (plus other not so relevant tables like, country, exchange...). The stocks table contains about 18000 rows, the groups table around 3000-ish and the indicators table is a bit over 710,000 rows. All tables are using auto-generated id columns and I've created an extra index in the indicators table comprised of (id, stock_id and definition_id). If I remove the indicators from the query it executes in 0.030 secs, which is what I'd expect, so my guess is that there must be something wrong there, but my knowledge of databases is somewhat limited and I'm stuck in search of a solution.

Any help will be much appreciated,

Thanks in advance,

Xabier.

The query:

explain select
    this_.id as id232_12_,
    this_.created_on as created2_232_12_,
    this_.updated_on as updated3_232_12_,
    this_.version as version232_12_,
    this_.autoupdate as autoupdate232_12_,
    this_.bloomberg as bloomberg232_12_,
    this_.currency_id as currency19_232_12_,
    this_.cusip as cusip232_12_,
    this_.disabled as disabled232_12_,
    this_.exchange_id as exchange20_232_12_,
    this_.isin as isin232_12_,
    this_.name as name232_12_,
    this_.price_composition as price11_232_12_,
    this_.region_id as region21_232_12_,
    this_.reuters as reuters232_12_,
    this_.sedol as sedol232_12_,
    this_.status_message_last_update as status14_232_12_,
    this_.status_message_severity as status15_232_12_,
    this_.status_message_text as status16_232_12_,
    this_.ticker as ticker232_12_,
    this_.trading_days_id as trading22_232_12_,
    this_.type as type232_12_,
    currency4_.id as id220_0_,
    currency4_.created_on as created2_220_0_,
    currency4_.updated_on as updated3_220_0_,
    currency4_.version as version220_0_,
    currency4_.code as code220_0_,
    currency4_.long_name as long6_220_0_,
    currency4_.short_name as short7_220_0_,
    currency4_.symbol as symbol220_0_,
    groups5_.stock_id as stock1_14_,
    groups_als1_.id as group2_14_,
    groups_als1_.id as id223_1_,
    groups_als1_.created_on as created3_223_1_,
    groups_als1_.updated_on as updated4_223_1_,
    groups_als1_.version as version223_1_,
    groups_als1_.active as active223_1_,
    groups_als1_.alt_name as alt7_223_1_,
    groups_als1_.code as code223_1_,
    groups_als1_.locked as locked223_1_,
    groups_als1_.name as name223_1_,
    groups_als1_.parent_id as parent12_223_1_,
    groups_als1_.position as position223_1_,
    groups_als1_.primary_index_id as primary13_223_1_,
    groups_als1_.type_id as type14_223_1_,
    groups_als1_.kind as kind223_1_,
    parent_als2_.id as id223_2_,
    parent_als2_.created_on as created3_223_2_,
    parent_als2_.updated_on as updated4_223_2_,
    parent_als2_.version as version223_2_,
    parent_als2_.active as active223_2_,
    parent_als2_.alt_name as alt7_223_2_,
    parent_als2_.code as code223_2_,
    parent_als2_.locked as locked223_2_,
    parent_als2_.name as name223_2_,
    parent_als2_.parent_id as parent12_223_2_,
    parent_als2_.position as position223_2_,
    parent_als2_.primary_index_id as primary13_223_2_,
    parent_als2_.type_id as type14_223_2_,
    parent_als2_.kind as kind223_2_,
    stock8_.id as id232_3_,
    stock8_.created_on as created2_232_3_,
    stock8_.updated_on as updated3_232_3_,
    stock8_.version as version232_3_,
    stock8_.autoupdate as autoupdate232_3_,
    stock8_.bloomberg as bloomberg232_3_,
    stock8_.currency_id as currency19_232_3_,
    stock8_.cusip as cusip232_3_,
    stock8_.disabled as disabled232_3_,
    stock8_.exchange_id as exchange20_232_3_,
    stock8_.isin as isin232_3_,
    stock8_.name as name232_3_,
    stock8_.price_composition as price11_232_3_,
    stock8_.region_id as region21_232_3_,
    stock8_.reuters as reuters232_3_,
    stock8_.sedol as sedol232_3_,
    stock8_.status_message_last_update as status14_232_3_,
    stock8_.status_message_severity as status15_232_3_,
    stock8_.status_message_text as status16_232_3_,
    stock8_.ticker as ticker232_3_,
    stock8_.trading_days_id as trading22_232_3_,
    stock8_.type as type232_3_,
    grouptype9_.id as id224_4_,
    grouptype9_.created_on as created2_224_4_,
    grouptype9_.updated_on as updated3_224_4_,
    grouptype9_.version as version224_4_,
    grouptype9_.name as name224_4_,
    components10_.virtual_group_id as virtual2_14_,
    components10_.group_id as group1_14_,
    components10_.group_id as group1_225_5_,
    components10_.virtual_group_id as virtual2_225_5_,
    components10_.operator as operator225_5_,
    components10_.operator_name as operator4_225_5_,
    components10_.sequence as sequence225_5_,
    indicators11_.stock_id as stock23_15_,
    indicators11_.id as id15_,
    indicators11_.id as id226_6_,
    indicators11_.created_on as created3_226_6_,
    indicators11_.updated_on as updated4_226_6_,
    indicators11_.version as version226_6_,
    indicators11_.definition_id as definition22_226_6_,
    indicators11_.stock_id as stock23_226_6_,
    indicators11_.dbl_delta1 as dbl6_226_6_,
    indicators11_.dbl_value1 as dbl7_226_6_,
    indicators11_.bool_delta1 as bool8_226_6_,
    indicators11_.bool_value1 as bool9_226_6_,
    indicators11_.bool_delta2 as bool10_226_6_,
    indicators11_.bool_value2 as bool11_226_6_,
    indicators11_.int_delta1 as int12_226_6_,
    indicators11_.int_value1 as int13_226_6_,
    indicators11_.dbl_delta2 as dbl14_226_6_,
    indicators11_.dbl_value2 as dbl15_226_6_,
    indicators11_.dbl_delta3 as dbl16_226_6_,
    indicators11_.dbl_value3 as dbl17_226_6_,
    indicators11_.date_set as date18_226_6_,
    indicators11_.relative_id as relative24_226_6_,
    indicators11_.is_support as is19_226_6_,
    indicators11_.int_delta2 as int20_226_6_,
    indicators11_.int_value2 as int21_226_6_,
    indicators11_.type as type226_6_,
    indicatord12_.id as id227_7_,
    indicatord12_.created_on as created2_227_7_,
    indicatord12_.updated_on as updated3_227_7_,
    indicatord12_.version as version227_7_,
    indicatord12_.code as code227_7_,
    indicatord12_.descrip as descrip227_7_,
    indicatord12_.format as format227_7_,
    indicatord12_.name as name227_7_,
    indicatord12_.numberformat as numberfo9_227_7_,
    stock13_.id as id232_8_,
    stock13_.created_on as created2_232_8_,
    stock13_.updated_on as updated3_232_8_,
    stock13_.version as version232_8_,
    stock13_.autoupdate as autoupdate232_8_,
    stock13_.bloomberg as bloomberg232_8_,
    stock13_.currency_id as currency19_232_8_,
    stock13_.cusip as cusip232_8_,
    stock13_.disabled as disabled232_8_,
    stock13_.exchange_id as exchange20_232_8_,
    stock13_.isin as isin232_8_,
    stock13_.name as name232_8_,
    stock13_.price_composition as price11_232_8_,
    stock13_.region_id as region21_232_8_,
    stock13_.reuters as reuters232_8_,
    stock13_.sedol as sedol232_8_,
    stock13_.status_message_last_update as status14_232_8_,
    stock13_.status_message_severity as status15_232_8_,
    stock13_.status_message_text as status16_232_8_,
    stock13_.ticker as ticker232_8_,
    stock13_.trading_days_id as trading22_232_8_,
    stock13_.type as type232_8_,
    stockregio14_.id as id218_9_,
    stockregio14_.created_on as created3_218_9_,
    stockregio14_.updated_on as updated4_218_9_,
    stockregio14_.version as version218_9_,
    stockregio14_.code as code218_9_,
    stockregio14_.name as name218_9_,
    stockregio14_.group_id as group9_218_9_,
    stockregio14_.type as type218_9_,
    group15_.id as id223_10_,
    group15_.created_on as created3_223_10_,
    group15_.updated_on as updated4_223_10_,
    group15_.version as version223_10_,
    group15_.active as active223_10_,
    group15_.alt_name as alt7_223_10_,
    group15_.code as code223_10_,
    group15_.locked as locked223_10_,
    group15_.name as name223_10_,
    group15_.parent_id as parent12_223_10_,
    group15_.position as position223_10_,
    group15_.primary_index_id as primary13_223_10_,
    group15_.type_id as type14_223_10_,
    group15_.kind as kind223_10_,
    tradingday16_.id as id233_11_,
    tradingday16_.created_on as created2_233_11_,
    tradingday16_.updated_on as updated3_233_11_,
    tradingday16_.version as version233_11_,
    tradingday16_.traded_on_friday as traded5_233_11_,
    tradingday16_.traded_on_monday as traded6_233_11_,
    tradingday16_.traded_on_saturday as traded7_233_11_,
    tradingday16_.traded_on_sunday as traded8_233_11_,
    tradingday16_.traded_on_thursday as traded9_233_11_,
    tradingday16_.traded_on_tuesday as traded10_233_11_,
    tradingday16_.traded_on_wednesday as traded11_233_11_ 
from
    stocks this_ 
left outer join
    currencies currency4_ 
        on this_.currency_id=currency4_.id 
inner join
    stocks_groups groups5_ 
        on this_.id=groups5_.stock_id 
inner join
    groups groups_als1_ 
        on groups5_.group_id=groups_als1_.id 
inner join
    groups parent_als2_ 
        on groups_als1_.parent_id=parent_als2_.id 
left outer join
    stocks stock8_ 
        on groups_als1_.primary_index_id=stock8_.id 
left outer join
    group_types grouptype9_ 
        on groups_als1_.type_id=grouptype9_.id 
left outer join
    virtual_groups_components components10_ 
        on groups_als1_.id=components10_.virtual_group_id 
left outer join
    indicators indicators11_ 
        on this_.id=indicators11_.stock_id 
left outer join
    indicator_definitions indicatord12_ 
        on indicators11_.definition_id=indicatord12_.id 
left outer join
    stocks stock13_ 
        on indicators11_.relative_id=stock13_.id 
inner join
    regions stockregio14_
        on this_.region_id=stockregio14_.id 
left outer join
    groups group15_ 
        on stockregio14_.group_id=group15_.id 
inner join
    stock_trading_days tradingday16_ 
        on this_.trading_days_id=tradingday16_.id
where
    (
        groups_als1_.id in (
            208, 193, 224, 1745, 216, 1746, 1793, 218, 1747, 223, 204, 203, 209, 217, 1547, 1326, 127, 1744, 210, 212, 202, 1325, 2051, 215, 185, 1720, 197, 1721, 205, 1749, 194, 211, 195, 206, 1323, 184, 213, 220, 201, 207, 219, 1748, 196, 1071, 200
        )
    ) 
order by
    parent_als2_.position asc,
    groups_als1_.position asc,
    this_.name asc;

I'm pasting the results from explain as csv because I don't know of any other way of presenting it in a more easy-to-work-with format.

The output from explain:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","groups_als1_","range","PRIMARY,FKB63DD9D428E54565","PRIMARY","8","",45,"Using where; Using temporary; Using filesort"
1,"SIMPLE","parent_als2_","eq_ref","PRIMARY","PRIMARY","8","pr2.groups_als1_.parent_id",1,""
1,"SIMPLE","stock8_","eq_ref","PRIMARY","PRIMARY","8","pr2.groups_als1_.primary_index_id",1,""
1,"S开发者_运维问答IMPLE","grouptype9_","eq_ref","PRIMARY","PRIMARY","8","pr2.groups_als1_.type_id",1,""
1,"SIMPLE","components10_","ref","FK91F4CE2D598958ED","FK91F4CE2D598958ED","8","pr2.groups_als1_.id",1,""
1,"SIMPLE","groups5_","ref","PRIMARY,FKA35A80369A4E438E,FKA35A8036A0A8A367,FKA35A8036CBAD0B70","FKA35A8036CBAD0B70","8","pr2.groups_als1_.id",4,"Using index"
1,"SIMPLE","tradingday16_","ALL","PRIMARY","","","",2,"Using join buffer"
1,"SIMPLE","this_","eq_ref","PRIMARY,FKCAD3EC1D1A5585AA,FKCAD3EC1DE15DC635","PRIMARY","8","pr2.groups5_.stock_id",1,"Using where"
1,"SIMPLE","currency4_","eq_ref","PRIMARY","PRIMARY","8","pr2.this_.currency_id",1,""
1,"SIMPLE","stockregio14_","eq_ref","PRIMARY","PRIMARY","8","pr2.this_.region_id",1,""
1,"SIMPLE","group15_","eq_ref","PRIMARY","PRIMARY","8","pr2.stockregio14_.group_id",1,""
1,"SIMPLE","indicators11_","ref","FKDC680444A0A8A367","FKDC680444A0A8A367","9","pr2.groups5_.stock_id",21,""
1,"SIMPLE","indicatord12_","eq_ref","PRIMARY","PRIMARY","8","pr2.indicators11_.definition_id",1,""
1,"SIMPLE","stock13_","eq_ref","PRIMARY","PRIMARY","8","pr2.indicators11_.relative_id",1,""

OK, thats a pretty big query, however, it will depend on the indexes on each table, and if you move the where clause to the join on the als1 table, that should help as currently you're making the mother of all joins, and then right at the end saying "Oh and I only want this bit"

Could you confirm all the id fields have indexes?

I can only ASSUME each table's "auto-increment" column "ID" has an index on it respectively. In addition, I would ensure Stock_Groups table has index on Group_ID... On virtual_groups_components, an index on virtual_group_id ON Indicators, an index on stock_id

Additionally, since you have so many join combinations, the query optimizer might be trying to think too much for you on which table SHOULD be used first and getting a bad result. I have sorted the tables out and moved your "Groups" table (alias 1 version) to the top since that is the basis of your WHERE clause. Also, I've added the clause "STRAIGHT_JOIN" which tells the compiler to run the query in the exact order listed... hence the "groups_als1_" version will be queried first, applied with the where, get the smallest set of records you would expect, THEN join to all the other lookup tables to get your result... Obviously, replace my placeholder of "AllYourFields". I'd be interested in the results. I've done this before with gov't data mining of 14+ million records with 15+ lookup tables and took a query from failing after 30+ hours down to less than 2 hrs (yes, retrieving and sorting ALL the records).

SELECT STRAIGHT_JOIN 
      PrimaryQuery.*,

      currency4_.id as id220_0_,
      currency4_.created_on as created2_220_0_,
      currency4_.updated_on as updated3_220_0_,
      currency4_.version as version220_0_,
      currency4_.code as code220_0_,
      currency4_.long_name as long6_220_0_,
      currency4_.short_name as short7_220_0_,
      currency4_.symbol as symbol220_0_,

      indicators11_.stock_id as stock23_15_,
      indicators11_.id as id15_,
      indicators11_.id as id226_6_,
      indicators11_.created_on as created3_226_6_,
      indicators11_.updated_on as updated4_226_6_,
      indicators11_.version as version226_6_,
      indicators11_.definition_id as definition22_226_6_,
      indicators11_.stock_id as stock23_226_6_,
      indicators11_.dbl_delta1 as dbl6_226_6_,
      indicators11_.dbl_value1 as dbl7_226_6_,
      indicators11_.bool_delta1 as bool8_226_6_,
      indicators11_.bool_value1 as bool9_226_6_,
      indicators11_.bool_delta2 as bool10_226_6_,
      indicators11_.bool_value2 as bool11_226_6_,
      indicators11_.int_delta1 as int12_226_6_,
      indicators11_.int_value1 as int13_226_6_,
      indicators11_.dbl_delta2 as dbl14_226_6_,
      indicators11_.dbl_value2 as dbl15_226_6_,
      indicators11_.dbl_delta3 as dbl16_226_6_,
      indicators11_.dbl_value3 as dbl17_226_6_,
      indicators11_.date_set as date18_226_6_,
      indicators11_.relative_id as relative24_226_6_,
      indicators11_.is_support as is19_226_6_,
      indicators11_.int_delta2 as int20_226_6_,
      indicators11_.int_value2 as int21_226_6_,
      indicators11_.type as type226_6_,

      indicatord12_.id as id227_7_,
      indicatord12_.created_on as created2_227_7_,
      indicatord12_.updated_on as updated3_227_7_,
      indicatord12_.version as version227_7_,
      indicatord12_.code as code227_7_,
      indicatord12_.descrip as descrip227_7_,
      indicatord12_.format as format227_7_,
      indicatord12_.name as name227_7_,
      indicatord12_.numberformat as numberfo9_227_7_,

      stock13_.id as id232_8_,
      stock13_.created_on as created2_232_8_,
      stock13_.updated_on as updated3_232_8_,
      stock13_.version as version232_8_,
      stock13_.autoupdate as autoupdate232_8_,
      stock13_.bloomberg as bloomberg232_8_,
      stock13_.currency_id as currency19_232_8_,
      stock13_.cusip as cusip232_8_,
      stock13_.disabled as disabled232_8_,
      stock13_.exchange_id as exchange20_232_8_,
      stock13_.isin as isin232_8_,
      stock13_.name as name232_8_,
      stock13_.price_composition as price11_232_8_,
      stock13_.region_id as region21_232_8_,
      stock13_.reuters as reuters232_8_,
      stock13_.sedol as sedol232_8_,
      stock13_.status_message_last_update as status14_232_8_,
      stock13_.status_message_severity as status15_232_8_,
      stock13_.status_message_text as status16_232_8_,
      stock13_.ticker as ticker232_8_,
      stock13_.trading_days_id as trading22_232_8_,
      stock13_.type as type232_8_,

      group15_.id as id223_10_,
      group15_.created_on as created3_223_10_,
      group15_.updated_on as updated4_223_10_,
      group15_.version as version223_10_,
      group15_.active as active223_10_,
      group15_.alt_name as alt7_223_10_,
      group15_.code as code223_10_,
      group15_.locked as locked223_10_,
      group15_.name as name223_10_,
      group15_.parent_id as parent12_223_10_,
      group15_.position as position223_10_,
      group15_.primary_index_id as primary13_223_10_,
      group15_.type_id as type14_223_10_,
      group15_.kind as kind223_10_

   FROM
      ( SELECT STRAIGHT_JOIN 
                groups_als1_.id as group2_14_,
                groups_als1_.id as id223_1_,
                groups_als1_.created_on as created3_223_1_,
                groups_als1_.updated_on as updated4_223_1_,
                groups_als1_.version as version223_1_,
                groups_als1_.active as active223_1_,
                groups_als1_.alt_name as alt7_223_1_,
                groups_als1_.code as code223_1_,
                groups_als1_.locked as locked223_1_,
                groups_als1_.name as name223_1_,
                groups_als1_.parent_id as parent12_223_1_,
                groups_als1_.position as position223_1_,
                groups_als1_.primary_index_id as primary13_223_1_,
                groups_als1_.type_id as type14_223_1_,
                groups_als1_.kind as kind223_1_,

                parent_als2_.id as id223_2_,
                parent_als2_.created_on as created3_223_2_,
                parent_als2_.updated_on as updated4_223_2_,
                parent_als2_.version as version223_2_,
                parent_als2_.active as active223_2_,
                parent_als2_.alt_name as alt7_223_2_,
                parent_als2_.code as code223_2_,
                parent_als2_.locked as locked223_2_,
                parent_als2_.name as name223_2_,
                parent_als2_.parent_id as parent12_223_2_,
                parent_als2_.position as position223_2_,
                parent_als2_.primary_index_id as primary13_223_2_,
                parent_als2_.type_id as type14_223_2_,
                parent_als2_.kind as kind223_2_,

                groups5_.stock_id as stock1_14_,

                this_.id as id232_12_,
                this_.created_on as created2_232_12_,
                this_.updated_on as updated3_232_12_,
                this_.version as version232_12_,
                this_.autoupdate as autoupdate232_12_,
                this_.bloomberg as bloomberg232_12_,
                this_.currency_id as currency19_232_12_,
                this_.cusip as cusip232_12_,
                this_.disabled as disabled232_12_,
                this_.exchange_id as exchange20_232_12_,
                this_.isin as isin232_12_,
                this_.name as name232_12_,
                this_.price_composition as price11_232_12_,
                this_.region_id as region21_232_12_,
                this_.reuters as reuters232_12_,
                this_.sedol as sedol232_12_,
                this_.status_message_last_update as status14_232_12_,
                this_.status_message_severity as status15_232_12_,
                this_.status_message_text as status16_232_12_,
                this_.ticker as ticker232_12_,
                this_.trading_days_id as trading22_232_12_,
                this_.type as type232_12_,

                stockregio14_.id as id218_9_,
                stockregio14_.created_on as created3_218_9_,
                stockregio14_.updated_on as updated4_218_9_,
                stockregio14_.version as version218_9_,
                stockregio14_.code as code218_9_,
                stockregio14_.name as name218_9_,
                stockregio14_.group_id as group9_218_9_,
                stockregio14_.type as type218_9_,

                tradingday16_.id as id233_11_,
                tradingday16_.created_on as created2_233_11_,
                tradingday16_.updated_on as updated3_233_11_,
                tradingday16_.version as version233_11_,
                tradingday16_.traded_on_friday as traded5_233_11_,
                tradingday16_.traded_on_monday as traded6_233_11_,
                tradingday16_.traded_on_saturday as traded7_233_11_,
                tradingday16_.traded_on_sunday as traded8_233_11_,
                tradingday16_.traded_on_thursday as traded9_233_11_,
                tradingday16_.traded_on_tuesday as traded10_233_11_,
                tradingday16_.traded_on_wednesday as traded11_233_11_,

                stock8_.id as id232_3_,
                stock8_.created_on as created2_232_3_,
                stock8_.updated_on as updated3_232_3_,
                stock8_.version as version232_3_,
                stock8_.autoupdate as autoupdate232_3_,
                stock8_.bloomberg as bloomberg232_3_,
                stock8_.currency_id as currency19_232_3_,
                stock8_.cusip as cusip232_3_,
                stock8_.disabled as disabled232_3_,
                stock8_.exchange_id as exchange20_232_3_,
                stock8_.isin as isin232_3_,
                stock8_.name as name232_3_,
                stock8_.price_composition as price11_232_3_,
                stock8_.region_id as region21_232_3_,
                stock8_.reuters as reuters232_3_,
                stock8_.sedol as sedol232_3_,
                stock8_.status_message_last_update as status14_232_3_,
                stock8_.status_message_severity as status15_232_3_,
                stock8_.status_message_text as status16_232_3_,
                stock8_.ticker as ticker232_3_,
                stock8_.trading_days_id as trading22_232_3_,
                stock8_.type as type232_3_,

                grouptype9_.id as id224_4_,
                grouptype9_.created_on as created2_224_4_,
                grouptype9_.updated_on as updated3_224_4_,
                grouptype9_.version as version224_4_,
                grouptype9_.name as name224_4_,

                components10_.virtual_group_id as virtual2_14_,
                components10_.group_id as group1_14_,
                components10_.group_id as group1_225_5_,
                components10_.virtual_group_id as virtual2_225_5_,
                components10_.operator as operator225_5_,
                components10_.operator_name as operator4_225_5_,
                components10_.sequence as sequence225_5_

            from
                groups groups_als1_ 
                    join groups parent_als2_ 
                        on groups_als1_.parent_id = parent_als2_.id 

                    join stocks_groups groups5_ 
                        on groups_als1_.id = groups5_.group_id

                    join stocks this_
                        on groups5_.stock_id = this_.id

                    join regions stockregio14_
                        on this_.region_id = stockregio14_.id 

                    join stock_trading_days tradingday16_ 
                        on this_.trading_days_id = tradingday16_.id

                    left outer join stocks stock8_ 
                        on groups_als1_.primary_index_id = stock8_.id 

                    left outer join group_types grouptype9_ 
                        on groups_als1_.type_id=grouptype9_.id 

                    left outer join virtual_groups_components components10_ 
                        on groups_als1_.id=components10_.virtual_group_id 

            where
                groups_als1_.id in ( 208, 193, 224, 1745, 216, 1746, 
                     1793, 218, 1747, 223, 204, 203, 209, 217, 1547, 
                     1326, 127, 1744, 210, 212, 202, 1325, 2051, 215, 
                     185, 1720, 197, 1721, 205, 1749, 194, 211, 195, 
                     206, 1323, 184, 213, 220, 201, 207, 219, 1748, 
                     196, 1071, 200 )
            order by
                parent_als2_.position asc,
                groups_als1_.position asc,
                this_.name asc; ) PrimaryQuery

        left outer join currencies currency4_ 
            on PrimaryQuery.currency19_232_12_ = currency4_.id 

        left outer join indicators indicators11_ 
            on PrimaryQuery.id232_12_ = indicators11_.stock_id 

            left outer join indicator_definitions indicatord12_ 
                on indicators11_.definition_id = indicatord12_.id 

            left outer join stocks stock13_ 
                on indicators11_.relative_id = stock13_.id 

        join regions stockregio14_
            on PrimaryQuery.region21_232_12_ = stockregio14_.id 

            left outer join groups group15_ 
                on stockregio14_.group_id = group15_.id 

        left outer join stocks stock8_ 
            on PrimaryQuery.primary13_223_1_ = stock8_.id 

Try putting your WHERE condition as part of your JOIN conditions instead.

Edit: I don't know if you can force Hibernate to do that

Since this has a lot of JOIN operations and you think the problem is related to indicators, I suggest you try removing one by one, every join that is related to indicators:

-Remove only the join indicator_definitions :

left outer join
    indicator_definitions indicatord12_ 
        on indicators11_.definition_id=indicatord12_.id 

-Remove only the join stocks stock13_ :

left outer join
    stocks stock13_ 
        on indicators11_.relative_id=stock13_.id 

-Remove both:

left outer join
    indicator_definitions indicatord12_ 
        on indicators11_.definition_id=indicatord12_.id 
left outer join
    stocks stock13_ 
        on indicators11_.relative_id=stock13_.id 

And a question: are all the LEFT JOINs necessary? Can some be turned to INNER JOINs?

It's hard to tell, without the actual data available, but often not using a full table scan might can be the problem.

If your select returns a large part of a table it should use a full table scan. From what you showed I can't tell if this applies in your case, nor do I know if you can control the join/select strategy used by mysql. (like with oracle hints)

--- update after actually looking at the sql statement ---

• There are lots of outer joins. Check these are actually necessary, i.e. the respective hibernate attributes are actually nullable. Some sound like they are actually not nullable

• groups are joined three times into the mix. You might be better of not joining them (at least not all three) but relying on lazy loading + the first level cache of hibernate.

• same might apply to stocks

• if I got it right there is a set of indicators belonging to each stock. I don't know if there is something like a clustered table in mysql as in oracle. It might help, though it is likely to cause problems else where.

• Another thing you should check is the cache hit rate inside the database. Again I don't know mysql, but maybe it is just running out of cache space so it has to load stuff into memory over and over again. More main memory and appropriate configuration of the db might help.