MySQL Statement extremely slow even with indexes

Question

The following query takes around 200 seconds to complete. What i'm trying to achieve is get users who have made 6 or more payments, who have not made any orders yet (there are 2 orders tables for different marketplaces).

u.id, ju.id are both primary keys.

I've indexed the user_id and order_status combined into one index on both orders tables. If I remove the join and COUNT() on the mp_orders table, the query takes 8 seconds to complete, but with it, it takes too long. I think i've indexed every thing that I could have but I don't understand why it takes so long to complete. Any ideas?

SELECT 
    u.id, 
    ju.name,
    COUNT(p.id) as payment_count, 
    COUNT(o.id) as order_count,
    COUNT(mi.id) as marketplace_order_count
FROM users as u
    INNER JOIN users2 as ju
        ON u.id = ju.id
    INNER JOIN payments as p
        ON u.id = p.user_id
    LEFT OUTER JOIN orders as o
        ON u.id = o.user_id
            AND o.order_status = 1
    LEFT OUTER JOIN mp_orders as mi
        ON u.id = mi.producer
            AND mi.order_status = 1
WHERE u.package != 1
AND u.enabled = 1
AND u.chart_ban = 0
GROUP BY u.id
HAVING COUNT(p.id) >= 6
    AND COUNT(o.id) = 0
    AND COUNT(mi.id) = 0
LIMIT 10

payments table

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | bigint(255)   | NO   | PRI | NULL    | auto_increment |
| user_id         | bigint(255)   | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

orders table (mp_orders table pretty much the same)

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(255)      | NO   | PRI | NULL    | auto_increment |
| order_number    | varchar(1024) | NO   | MUL | NULL    |                |
| user_id         | int(255)      | NO   | MUL | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

Answer 1

You don't need to COUNT the rows of your orders, you need to retrieve users which doesn't have orders, that's not really the same thing.

Instead of counting, filter the users which have no orders :

SELECT 
    u.id, 
    ju.name,
    COUNT(p.id) as payment_count
FROM users as u
    INNER JOIN users2 as ju
        ON u.id = ju.id
    INNER JOIN payments as p
        ON u.id = p.user_id
    LEFT OUTER JOIN orders as o
        ON u.id = o.user_id
            AND o.order_status = 1
    LEFT OUTER JOIN mp_orders as mi
        ON u.id = mi.producer
            AND mi.order_status = 1
WHERE u.package != 1
AND u.enabled = 1
AND u.chart_ban = 0
AND o.id IS NULL    -- filter happens here
AND mi.id IS NULL   -- and here
GROUP BY u.id
HAVING COUNT(p.id) >= 6
LIMIT 10

This will prevent the engine to count each of the orders for each of your users, and you will gain a lot of time.

One can think that the engine should use the index for doing the count, and so the count must be fast enough.
I will quote from a different site: InnoDB COUNT(id) - Why so slow?

It may be to do with the buffering, InnoDb does not cache the index it caches into memory the actual data rows, because of this for what seems to be a simple scan it is not loading the primary key index but all the data into RAM and then running your query on it. This may take some time to work - hopefully if you were running queries after this on the same table then they would run much faster.

MyIsam loads the indexes into RAM and then runs its calculations over this space and then returns a result, as an index is generally much much smaller than all the data in the table you should see an immediate difference there.

Another option may be the way that innodb stores the data on the disk - the innodb files are a virtual tablespace and as such are not necessarily ordered by the data in your table, if you have a fragmented data file then this could be creating problems for your disk IO and as a result running slower. MyIsam generally are sequential files, and as such if you are using an index to access data the system knows exactly in what location on disk the row is located - you do not have this luxury with innodb, but I do not think this particular issue comes into play with just a simple count(*) ==================== http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html explains this:

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If your table does not change often, using the MySQL query cache is a good solution. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See Section 14.2.11, “InnoDB Performance Tuning Tips”. =================== todd_farmer:It actually does explain the difference - MyISAM understands that COUNT(ID) where ID is a PK column is the same as COUNT(*), which MyISAM keeps precalculated while InnoDB does not.