Re: Performance problem migrating from NDB to MyISAM

[Giuseppe Proietti]

Giuseppe Proietti Wrote:
-------------------------------------------------------
> Hi,
>
> Thanks, I'm now planning the update to a more
> recent version of MySQL, i.e. 5.5 or 5.6

Thanks a lot for your support.

I update to 5.6.22-1 and now the query performs very quickly

773 rows in set (0.06 sec)

As shown by the explain command, it still doesn't use indexes from the table in the nested select.


mysql> explain select id,user_login,user_email from wp_users where id in (select user_id from wp_wpsc_purchase_logs whe re from_unixtime(date) > date('2014-11-30') and processed=3);
+----+-------------+-----------------------+--------+--------------------+---------+---------+---------------------------------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+--------+--------------------+---------+---------+---------------------------------------------------+------+------------------------------+
| 1 | SIMPLE | wp_wpsc_purchase_logs | ALL | processed_and_date | NULL | NULL | NULL | 2984 | Using where; Start temporary |
| 1 | SIMPLE | wp_users | eq_ref | PRIMARY | PRIMARY | 8 | fondarching_cluster.wp_wpsc_purchase_logs.user_ID | 1 | End temporary |
+----+-------------+-----------------------+--------+--------------------+---------+---------+---------------------------------------------------+------+------------------------------+
2 rows in set (0.00 sec)