help with an index

Question

Ok, I'm not great in mysql, but I know an index would help me out here, however I've done some plugging and can't find one to help...

Anyone got any ideas?

    explain 
select `users_usr`.`id_usr` AS `id_usr`,
`users_usr`.`firstname_usr` AS `firstname_usr`,
`users_usr`.`lastname_usr` AS `lastname_usr`,`users_usr`.`social_usr` AS `social_usr`,`users_usr`.`address1_usr` AS `address1_usr`,
`users_usr`.`address2_usr` AS `address2_usr`,`users_usr`.`city_usr` AS `city_usr`,`users_usr`.`state_usr` AS `state_usr`,`users_usr`.`zip_usr` AS `zip_usr`,
`users_usr`.`email_usr` AS `email_usr`,`credit_acc`.`given_credit_acc` AS `given_credit_acc`,`credit_acc`.`credit_used_acc` AS `credit_used_acc`,
`credit_acc`.`date_established_acc` AS `date_established_acc`,`credit_acc`.`type_acc` AS `type_acc`,`credit_acc`.`bureau_status_acc` AS `bureau_status_acc`,
sum((`credit_balance`.`debit_acc` - `credit_balance`.`credit_acc`)) AS `balance` 



from (((`users_usr` 
left join `credit_acc` on((`users_usr`.`id_usr` = `credit_acc`.`uid_usr`))) 
left join `cfc_cfc` on((`credit_acc`.`id_cfc` = `cfc_cfc`.`id_cfc`))) 
join `credit_acc` `credit_balance` on((`credit_balance`.`credit_used_acc` = `credit_acc`.`id_acc`))) 

where ((`credit_acc`.`type_acc` = _latin1'init') 
and (`credit_acc`.`status_acc` = _latin1'active') 
and (`credit_acc`.`linetype_acc` = _latin1'personal')) 

group by `credit_balance`.`credit_used_acc` order by `users_usr`.`id_usr`

Gives me

id  select_type  table           type    possible_keys                        key              key_len  ref                                  rows  Extra                          
------  -----------  --------------  ------  -----------------------------------  ---------------  -------  ---------------------------------  ------  -------------------------------
     1  SIMPLE       credit_balance  index   credit_used_acc,cash_report_index    credit_used_acc  40       (NULL)                              14959  Using temporary; Using filesort
     1  SIMPLE       credit_acc      eq_ref  PRIMARY,type_acc,type_acc_2,uid_usr  PRIMARY          8        cc.credit_balance.credit_used_acc       1  Using where                    
     1  SIMPLE       cfc_cfc         eq_ref  PRIMARY                              PRIMARY          4        cc.credit_acc.id_cfc                    1  Using index                    
     1  SIMPLE       users_usr       eq_ref  PRIMARY,id_usr                       PRIMARY          4        cc.credit_acc.uid_usr                   1



Table       Non_unique  Key_name           Seq_in_index  Column_name              Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment
----------  ----------  -----------------  ------------  -----------------------  ---------  -----------  --------  ------  ------  ----------  -------
credit_acc           0  PRIMARY                       1  id_acc                   A                14016    (NULL)  (NULL)          BTREE              
credit_acc           1  type_acc                      1  type_acc                 A                   11    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  type_acc                      2  date_acc                 A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  type_acc                      3  affiliate_aff            A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  type_acc_2                    1  type_acc                 A                   11    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  type_acc_2                    2  date_acc                 A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  type_acc_2                    3  complete_acc             A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  type_acc_2                    4  commission_refunded_acc  A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  credit_used_acc               1  credit_used_acc          A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  credit_used_acc               2  id_acc                   A                14016    (NULL)  (NULL)          BTREE              
credit_acc           1  credit_used_acc               3  type_acc                 A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  uid_usr                       1  uid_usr                  A                 7008    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  cash_report_index             1  credit_used_acc          A                 7008    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  cash_report_index             2  type_acc                 A                14016    (NULL)  (NULL)  YES     BTREE              
credit_acc           1  cash_report_index             3  date_established_acc     A                14016    (NULL)  (NULL)  YES     BTREE

Answer 1

Your EXPLAIN output shows that you already have indexes that might be useful, but the query engine has decided not to use them.

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html says:

Using temporary

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

Your query includes:

GROUP BY `credit_balance`.`credit_used_acc` 
ORDER BY `users_usr`.`id_usr`

You name different columns in these two clauses, so this means the query requires a temporary table and sorts its results on disk. Disk I/O is the arch-enemy of SQL performance. This is probably more detrimental to performance than you can make up for by applying an index.

So I'd suggest try removing the ORDER BY clause and see if it gets rid of the "using temporary" comment.

edit Okay, I've done some more tests and looked more closely at your table defs.

I think you have a lot of indexes that are redundant, and not applicable to this current query. It could be the indexes are useful to some other query, or they may just be leftovers from your experiments.

One observation is that your join to cfc_cfc is unnecessary but I see from your comment that you've taken it out. It doesn't seem to fix the EXPLAIN report though.

Another observation is that your LEFT JOIN is unnecessary; it could be an INNER JOIN because you have conditions in the WHERE clause for those columns anyway. There's no purpose for it being an outer join, and outer joins tend to be slower.

Indexes may be useful for columns you use in join conditions, or in row restrictions, or in GROUP BY or ORDER BY clauses. MySQL cannot use more than one index per table in a given query, so it makes sense to define compound indexes. But you should define the index over columns that are used in the query. If you only use columns 2 and 3 of a three-column index (i.e. not the first column in the index) then the index is ineffective.

Of course there are indexes created implicitly for all primary key and foreign key constraints, but here's the only extra index I created:

KEY columns_used_in_query (uid_usr, type_acc, status_acc, linetype_acc),

It shouldn't matter to the optimization plan if you put your conditions in the WHERE clause or the join condition, as long as they aren't part of the conditions for an outer join. But I do notice that all other things being equal, the optimizer seems to choose an index based on which one you define first!

I still haven't eliminated the temp table & filesort comment in the EXPLAIN report, but I think these changes will speed up the query.

Answer 2

Retract some of that, and start over.

Here is my interpretation of your query.

uu.select uu.id_usr,
uu.firstname_usr,
uu.lastname_usr,
uu.social_usr,
uu.address1_usr,
uu.address2_usr,
uu.city_usr,
uu.state_usr,
uu.zip_usr,
uu.email_usr,
ca.given_credit_acc,
ca.credit_used_acc,
ca.date_established_acc,
ca.type_acc,
ca.bureau_status_acc,
sum(cb.debit_acc - cb.credit_acc) AS balance

from users_usr AS uu

left join credit_acc AS ca on uu.id_usr = ca.uid_usr

join credit_acc AS cb on ca.credit_used_acc = ca.id_acc

where ca.type_acc = 'init'
and ca.status_acc = 'active'
and ca.linetype_acc = 'personal'

group by cb.credit_used_acc
order by uu.id_usr

Answer 3

Your query (the optimizable part with the joins, wheres, groups and sorts) can be simplified to:

SELECT
uu.select uu.id_usr,
ca.given_credit_acc,
ca.credit_used_acc,
ca.type_acc,
sum(cb.debit_acc - cb.credit_acc) AS balance

FROM
users_usr AS uu

LEFT JOIN
credit_acc AS ca on uu.id_usr = ca.uid_usr
AND ca.type_acc = 'init'
AND ca.status_acc = 'active'
AND ca.linetype_acc = 'personal'
-- credit_acc needs an index on uid_usr + type_acc _ status_acc

JOIN
credit_acc AS cb on ca.credit_used_acc = ca.id_acc
-- credit_acc needs an index on credit_used_acc

GROUP BY cb.credit_used_acc
ORDER BY uu.id_usr

Notice that I took the WHERE clause and moved it into the JOIN - MySQL seems to like this sort of adjustment.

Notice the comments about indexes. See if you can get that simplification to work (and optimize), and then add back the other fields.

What do you think, Bill?