-1

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

SELECT transactions . * , m.member_id, m.group_id, m.username,    preferred_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_balance   1   check_balance   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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Oleksandr IY
  • 2,783
  • 6
  • 32
  • 53
  • 2
    Can you post the results from EXPLAIN? – Joe Stefanelli Jul 13 '17 at 19:37
  • 1
    index of paid_microtime? – Quazer Jul 13 '17 at 19:41
  • @JoeStefanelli Please check EDIT – Oleksandr IY Jul 13 '17 at 19:41
  • @Quazer yes its there seems lost on copy – Oleksandr IY Jul 13 '17 at 19:43
  • Doubt it helps but why do you have `transactions.payment_method` when you have `transactions.*`? is it fumbling on the duplicate column name? rebuild indexes on both tables especially for member_id and paid_microtime? – xQbert Jul 13 '17 at 19:53
  • @xQbert I replaced * with needed fields and it reduced time execution about to 20%. But I haven't understood about rebuild indexes, can you please explain more? – Oleksandr IY Jul 13 '17 at 20:11
  • https://stackoverflow.com/questions/30051510/how-can-i-rebuild-indexes-and-update-stats-in-mysql-innodb it's possible w/ deletes /inserts that the indexes for the tables and or table statistics have become corrupt. you may need to rebuild the indexes and rengerate the table statistics for the cost based optimizer to function correctly and https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html may help. you may want to start with just analyzing the table: https://stackoverflow.com/questions/30879308/rebuild-index-on-innodb – xQbert Jul 13 '17 at 20:13
  • https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html may also work if you've done alot of inserts/deletes in mass on the table. as it will re-arrange physical storage for faster access. kinda like a defrag for a database table. – xQbert Jul 13 '17 at 20:16
  • 1
    The only index that makes sense to use here is `paid_microtime`. If rebuilding the index doesn't help, try `straight_join` instead of `join`, or force that index with `... FROM transactions force index (paid_microtime) join members m ...` – Solarflare Jul 13 '17 at 20:18
  • rebuilding I did before and doesn't help – Oleksandr IY Jul 13 '17 at 20:29

1 Answers1

0

That was a lack of RAM problem.

Oleksandr IY
  • 2,783
  • 6
  • 32
  • 53