mysql - Sending data taking too long but indexes aready create -

i having problems query taking 20 seconds return results :(

in table cases , cases_cstm, have 960,000 rows

this query:

    select  cases.id ,cases_cstm.assunto_c, cases.name , cases.case_number ,       cases.priority  , accounts.name account_name ,        accounts.assigned_user_id account_name_owner  ,        'accounts' account_name_mod, cases.account_id  ,        ltrim(rtrim(concat(ifnull(jt1.first_name,''),' ',ifnull(jt1.last_name,'')))) assigned_user_name ,        jt1.created_by assigned_user_name_owner  ,        'users' assigned_user_name_mod, cases.status , cases.date_entered ,       cases.assigned_user_id      cases      left join cases_cstm on cases.id = cases_cstm.id_c    left join  accounts accounts on        cases.account_id=accounts.id , accounts.deleted=0   ,        accounts.deleted=0      left join  users jt1 on        cases.assigned_user_id=jt1.id ,        jt1.deleted=0   , jt1.deleted=0           (((ltrim(rtrim(concat(ifnull(accounts.name,'')))) 'rodrigo fernando%' or       ltrim(rtrim(concat(ifnull(accounts.name,'')))) 'rodrigo fernando%'))) ,       cases.deleted=0 order cases.date_entered desc limit 0,11; 

here indexes of table:

+-------+------------+--------------------+--------------+------------------ +-----------+-------------+----------+--------+------+------------+-------- | table | non_unique | key_name           | seq_in_index | column_name |collation | cardinality | sub_part | packed | null | index_type | comment  +-------+------------+--------------------+--------------+------------------ +-----------+-------------+----------+--------+------+------------+--------- | cases |          0 | primary            |            1 | id          |         |      911472 |     null | null   |      | btree      |           | cases |          0 | case_number        |            1 | case_number |         |      911472 |     null | null   |      | btree      |          | | cases |          1 | idx_case_name      |            1 | name        |         |      911472 |     null | null   | yes  | btree      |          | | cases |          1 | idx_account_id     |            1 | account_id  |         |      455736 |     null | null   | yes  | btree      |          | | cases |          1 | idx_cases_stat_del |            1 | assigned_user_id|         |         106 |     null | null   | yes  | btree      |          | | cases |          1 | idx_cases_stat_del |            2 | status      |         |         197 |     null | null   | yes  | btree      |          | | cases |          1 | idx_cases_stat_del |            3 | deleted     |         |         214 |     null | null   | yes  | btree      |          | | cases |          1 | idx_priority       |            1 | priority    |         |      455736 |     null | null   | yes  | btree      |          | | cases |          1 | idx_date_entered   |            1 | date_entered|         |      455736 |     null | null   | yes  | btree      |          +-------+------------+--------------------+--------------+------------------ +-----------+-------------+----------+--------+------+------------+--------- 

the explain command of query(image!)

this profile of query execution:

+--------------------+-----------+ | status             | duration  | +--------------------+-----------+ | starting           |  0.000122 | | opening tables     |  0.000180 | | system lock        |  0.000005 | | table lock         |  0.000005 | | init               |  0.000051 | | optimizing         |  0.000017 | | statistics         |  0.000071 | | preparing          |  0.000021 | | executing          |  0.000003 | | sorting result     |  0.000004 | | sending data       | 21.595455 | | end                |  0.000012 | | query end          |  0.000002 | | freeing items      |  0.000419 | | logging slow query |  0.000005 | | logging slow query |  0.000002 | | cleaning        |  0.000004 | 

can me undertang why query taking long execute?

thanks!!

first, change left join accounts inner join don't know if make drastic change, makes lot more sense if understand difference.

what saying left join "i want cases, whether or not have associated account". inner join here means "give me cases , return accounts them".

the end-result of query same, because later on filtering things out where clause, have feeling might why idx_account_id being ignored.

a second, bigger problem clause:

 (((ltrim(rtrim(concat(ifnull(accounts.name,'')))) 'rodrigo fernando%' or   ltrim(rtrim(concat(ifnull(accounts.name,'')))) 'rodrigo fernando%'))) , 

there's ton of functions here, , mysql can't optimize using index. every record checked condition, , functions you're using called every record. biggest problem.

first, can simplified bit. think both sides of or statements same, lets first turn one:

ltrim(rtrim(concat(ifnull(accounts.name,'')))) 'rodrigo fernando%' 

since adding wildcard on 1 side of switch statement, why bother rtrim?

ltrim(concat(ifnull(accounts.name,''))) 'rodrigo fernando%' 

you don't need concat anything, if there's 1 thing!

ltrim(ifnull(accounts.name,'')) 'rodrigo fernando%' 

ltrim works fine on null values

ltrim(accounts.name) 'rodrigo fernando%' 

alright, saved bunch of functions. however, last ltrim still major problem still blocks mysql using indexes. solution simple though:

1. update accounts table, once:

   update accounts set name = ltrim(name);

2. make sure whenever insert new accounts, trim before inserting. you're doing during insert time, not select time.

3. change previous clause to:

   accounts.name 'rodrigo fernando%'

boom, can use index on accounts.name , fast fuck.