Here's the SQL:
SELECT digital_downloads.title AS digital_downloads, addresses.title AS addresses, order_products.title AS order_products, CONCAT('Order #', orders.id) AS orders, `users`.`email`, `users`.`id`, `users`.`prechange_user`, `users`.`first_name`, `users`.`last_name`, `users`.`company`, countries.title AS country_id, phone_types.title AS phone_1_phone_type_id, phone_types.title AS phone_2_phone_type_id, phone_types.title AS phone_3_phone_type_id, CONCAT('Issue ', IF(issues.number < 10, '0', ''), issues.number) AS issues, old_volumes.title AS old_volumes, CONCAT(CAST(DATE_FORMAT(transactions.date, '%c/%e/%Y') AS BINARY), ': ', transactions.description) AS transactions, CONCAT('Email message #', email_messages.id) AS email_messages
FROM (users)
LEFT JOIN digital_downloads_users ON `users`.`id` = `digital_downloads_users`.`user_id`
LEFT JOIN digital_downloads ON `digital_downloads_users`.`digital_download_id` = `digital_downloads`.`id`
LEFT JOIN addresses ON users.id = addresses.user_id
LEFT JOIN order_products ON users.id = order_products.gift_user_id
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN countries ON users.country_id = countries.id
LEFT JOIN phone_types ON users.phone_1_phone_type_id = phone_types.id
LEFT JOIN issues_users ON `users`.`id` = `issues_users`.`user_id`
LEFT JOIN issues ON `issues_users`.`issue_id` = `issues`.`id`
LEFT JOIN old_volumes_users ON `users`.`id` = `old_volumes_users`.`user_id`
LEFT JOIN old_volumes ON `old_volumes_users`.`old_volume_id` = `old_volumes`.`id`
LEFT JOIN transactions ON users.id = transactions.user_id
LEFT JOIN email_messages ON users.id = email_messages.user_id
WHERE users.registered != '0000-00-00 00:00:00'
GROUP BY `users`.`id`
ORDER BY users.email, users.email, `users`.`id`
LIMIT 50
Here's the EXPLAIN:
+----+-------------+-------------------------+--------+---------------+--------------+---------+------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+---------------+--------------+---------+------------------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | users | index | NULL | PRIMARY | 4 | NULL | 1441 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | digital_downloads_users | ref | user_id | user_id | 4 | namarupa.users.id | 13 | |
| 1 | SIMPLE | digital_downloads | eq_ref | PRIMARY | PRIMARY | 4 | namarupa.digital_downloads_users.digital_download_id | 1 | |
| 1 | SIMPLE | addresses | ref | user_id | user_id | 4 | namarupa.users.id | 1 | |
| 1 | SIMPLE | order_products | ref | gift_user_id | gift_user_id | 4 | namarupa.users.id | 13 | |
| 1 | SIMPLE | orders | ref | user_id | user_id | 4 | namarupa.users.id | 5 | |
| 1 | SIMPLE | countries | eq_ref | PRIMARY | PRIMARY | 4 | namarupa.users.country_id | 1 | |
| 1 | SIMPLE | phone_types | eq_ref | PRIMARY | PRIMARY | 4 | namarupa.users.phone_1_phone_type_id | 1 | |
| 1 | SIMPLE | issues_users | ref | user_id | user_id | 4 | namarupa.users.id | 3 | |
| 1 | SIMPLE | issues | eq_ref | PRIMARY | PRIMARY | 4 | namarupa.issues_users.issue_id | 1 | |
| 1 | SIMPLE | old_volumes_users | ref | users_id | users_id | 4 | namarupa.users.id | 21 | Using index |
| 1 | SIMPLE | old_volumes | eq_ref | PRIMARY | PRIMARY | 4 | namarupa.old_volumes_users.old_volume_id | 1 | |
| 1 | SIMPLE | transactions | ref | user_id | user_id | 4 | namarupa.users.id | 2 | |
| 1 | SIMPLE | email_messages | ref | user_id | user_id | 4 | namarupa.users.id | 15 | |
+----+-------------+-------------------------+--------+---------------+--------------+---------+------------------------------------------------------+------+----------------------------------------------+
I understand that this is a pretty thick query but the EXPLAIN looks good, right? What could be wrong?