Oleksandr Khavdiy Oleksandr Khavdiy - 4 months ago 14
MySQL Question

How to optimize query with over 100k records

My query run more then 25 seconds and I can't understand why

SELECT transactions . * , m.member_id, m.group_id, m.username, preffered_currency, transactions.payment_method
FROM `transactions`
JOIN `members` `m` ON `m`.`member_id` = `transactions`.`member_id`
ORDER BY `paid_microtime`
LIMIT 25


Here is indexes from transaction(~100k records) table

transactions 0 PRIMARY 1 transaction_id A 92304 NULL NULL BTREE
transactions 1 member_id 1 member_id A 44 NULL NULL BTREE
transactions 1 payment_method 1 payment_method A 70 NULL NULL BTREE
transactions 1 child_method 1 child_method A 234 NULL NULL BTREE
transactions 1 check_ballance 1 check_ballance A 2 NULL NULL BTREE
transactions 1 transaction_status 1 transaction_status A 18 NULL NULL BTREE
transactions 1 dt 1 dt A 92304 NULL NULL BTREE
transactions 1 transaction_type 1 transaction_type A 2 NULL NULL BTREE
transactions 1 paid_microtime 1 paid_microtime A 92317 NULL NULL BTREE


And from members (30 records) table

members 0 PRIMARY 1 member_id A 28 NULL NULL BTREE
members 1 username 1 username A 28 NULL NULL BTREE
members 1 ban 1 ban A 4 NULL NULL BTREE
members 1 active 1 active A 4 NULL NULL BTREE


Can you see what is wrong with my query that it took 25 seconds for this?

EDIT:
Results from explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE m ALL PRIMARY NULL NULL NULL 28 Using temporary; Using filesort
1 SIMPLE transactions ref member_id member_id 4 tadam.m.member_id 2097 Using index condition

Answer Source

That was a lack of RAM problem.