1

when I use a MySQL query:

SELECT count(*) FROM orders

it takes 0.183 s

if I use the query:

SELECT count(*) FROM orders
LEFT  JOIN order_items ON (`orders`.`StoreOrderId` = order_items.StoreOrderId)
LEFT  JOIN seller_accounts ON (`orders`.`seller_id` = seller_accounts.seller_id)

it takes 4.793 s

after explain:

{table:orders,          select_type:simple, type:index, key_len:166, rows:841683, extra:using index}
{table:seller_accounts, select_type:simple, type:ref,   key_len:83,  rows:1, extra:using index}
{table:order_items,     select_type:simple, type:ref,   key_len:83,  rows:1, extra:using index}

this count() query is used for PHP pagination, I need to know how many records are in the database so that I can know how many pages.

but count() query took me 4.793s, which is too long, who can I make it faster?

Improve this question
5
  • 1
    Performance question need a EXPLAIN of the queries and create table for all tables invoved Commented Aug 2, 2021 at 18:30
  • 1
    Are you using indexes correctly? Commented Aug 2, 2021 at 18:32
  • please check my explain, I am using index and type is index or ref, seem not bad Commented Aug 2, 2021 at 18:35
  • I can't really think that this query does anything useful. Commented Aug 2, 2021 at 18:42
  • what are you trying to count? with the order_items join, your count of orders becomes a count of order items. and then the seller_accounts join multiplies it by the number of accounts the seller_id has (which maybe is always one)? anyway, what you are trying doesn't look like it makes a whole lot of sense; back up and describe what you want your query to do Commented Aug 2, 2021 at 18:48

3 Answers 3

Reset to default
2

First, I will answer your Question.

The single-table COUNT finds the smallest index and scans straight through it.

The multiple-table COUNT works something like this:

  1. For each row in orders...
  2. Reach into order_items using StoreOrderId, one row at a time. Hopefully, that column is indexed.
  3. Ditto for seller_accounts.

Note that scanning an index with 841683 items is a lot faster than looking up each one of 841683 (or more or less) one at a time.

Now I will rip into the question.

These indexes are needed for the JOINs:

 order_items:      INDEX(StoreOrderId)
 seller_accounts:  INDEX(seller_id)

Are there multiple "items" in an order? Then the second JOIN will give a bigger COUNT(*). Hence it is reasonable for it to take more time.

You want users to paginate through 841683 orders???

If there is a WHERE clause or anything else, we need to see it in order to advise on performance. And SHOW CREATE TABLE.

Why are the ids 83 bytes?

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

instead of star use some column

SELECT count(order_items) FROM orders
LEFT  JOIN order_items ON (`orders`.`StoreOrderId` = order_items.StoreOrderId)
LEFT  JOIN seller_accounts ON (`orders`.`seller_id` = seller_accounts.seller_id)
Improve this answer

2 Comments

* simply says count the rows. order_items says to count only rows where order_items is NOT NULL. * is usually the better approach.
Furthermore * can use any index; COUNT(order_items) must use an index that contains that column -- or default to the entire table, which is probably bulkier.
0

SELECT 'Orders' table name, COUNT() rows FROM orders UNION SELECT 'OrderItems'' table name, COUNT() rows FROM order_items UNION SELECT 'SellerAcc' table name, COUNT(*) rows FROM seller_accounts

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.