Help with the index
Ok, I'm not very good at mysql, but I know the index will help me here, however I have made multiple connections and cannot find one to help ...
Does anyone have any idea?
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
source to share
The EXPLAIN output shows that you already have indexes that might be useful, but the query engine decided not to use them.
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html says:
Use of temporary
To resolve a query, MySQL must create a temporary table for the result. This usually happens if the query contains a GROUP BY and an ORDER BY which render columns differently.
Your request includes:
GROUP BY `credit_balance`.`credit_used_acc`
ORDER BY `users_usr`.`id_usr`
You are naming different columns in the two clauses, so this means that the query requires a temporary table and sorts its results on disk. Disk I / O is the main opponent of SQL performance. This is probably more detrimental to performance than you can compensate for with an index.
So, I suggest trying to remove the sentence ORDER BY
and see if it gets rid of the "temporary" comment.
edit Ok, I did some more tests and took a closer look at your tables.
I think you have a lot of indexes that are redundant and not applicable to this current query. These could be indexes useful for some other query, or they could just be leftovers from your experiments.
One observation is that you cfc_cfc
don't need to join , but I can see from your comment that you accepted it. It looks like it doesn't fix the EXPLAIN report.
Another observation is that yours is LEFT JOIN
not needed; it might be INNER JOIN
because you have conditions in the sentence WHERE
for those columns. There is no purpose as this is an outer join, and outer joins are generally slower.
Indexes can be useful on columns that you use in join conditions, or in row constraints, 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 composite indexes. But you have to determine the index on the columns that are used in the query. If you only use columns 2 and 3 of a 3-column index (i.e. not the first column in the index), then the index is ineffective.
Of course, indexes are created implicitly for all primary key and foreign key constraints, but here's the only additional index I've created:
KEY columns_used_in_query (uid_usr, type_acc, status_acc, linetype_acc),
You should not consider an optimization plan if you put your conditions in a proposal WHERE
or join condition if they are not part of the outer join conditions. But I notice that all other things being equal, the optimizer seems to pick the index from which you determine the first one!
I still haven't excluded temp comment and filesort comment in the EXPLAIN report, but I think these changes will speed up the request.
source to share
Take out some of this and start over.
Here is my interpretation of your request.
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_usrredit,
uu.emacail_cusr,
ca. ,
ca.date_established_acc,
ca.type_acc,
ca.bureau_status_acc,
sum (cb.debit_acc - cb.credit_acc) ASbalance
from users_usr AS uu
left join credit_acc AS ca on uu.id_usr = ca.uid_usr
Append credit_acc AS cb to 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
source to share
Your query (the optimized part with unions, 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) ASbalance
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 for uid_usr + type_acc _ status_acc
JOIN
credit_acc AS cb at ca.credit_used_acc = ca.id_acc
- credit_acc needs an index for credit_used_acc
GROUP BY cb.credit_used_acc
ORDER BY uu.id_usr
Note that I took the WHERE clause and moved it to JOIN. It seems that MySQL is like this kind of setup.
Pay attention to the comments about indexes. See if you can make things easier (and optimized) and then add other fields.
What do you think Bill?
source to share