The following query is run in a MySQL database (all InnoDB tables, Percona 5.5), and is slow for some parameter values (i.e. for some large customers). It returns the total size of all files uploaded by a given customer:

SELECT SUM(document.size)
  FROM document
       INNER JOIN post ON post.id = document.id
       INNER JOIN page ON page.id = post.page_id
 WHERE page.customer_id=1234;

I tried denormalization -- added document.customer_id column and index on document(customer_id, size) -- and got much better query times. But I'd like to avoid denormalization if possible. I also tried adding just an index on document(size), but couldn't get MySQL to use it. The documentation states that every index in an InnoDB table also contains references to the primary key, so why did MySQL not use this new index when joining, and save itself the need to load document row data?

The schema looks like:

  INT customer_id NOT NULL,
  INDEX ix_page_customer (customer_id)
  FOREIGN KEY (customer_id) REFERENCES customer (id)

  INDEX ix_post_page (page_id),
  FOREIGN KEY (page_id) REFERENCES page (id)

CREATE TABLE document (
  INT size NOT NULL,

A customer contains many pages, a page contains many posts. Posts can be of different types (e.g. message, document). There are tables for each of these post subtypes, of which document is one. One of the larger customers has 435K posts (of which 8K are documents) spread over 25K pages.

The query is fairly fast for smaller customers (i.e lower pages*posts), but slow for larger customers. Here's EXPLAIN output for the query for a large customer:

| id | select_type | table     | type   | possible_keys            | key              | key_len | ref                   | rows  | Extra       |
|  1 | SIMPLE      | page      | ref    | PRIMARY,ix_page_customer | ix_page_customer | 4       | const                 | 51200 | Using index |
|  1 | SIMPLE      | post      | ref    | PRIMARY,ix_post_page     | ix_post_page     | 4       | database.page.page_id |     2 | Using index |
|  1 | SIMPLE      | document  | eq_ref | PRIMARY                  | PRIMARY          | 4       | database.post.post_id |     1 |             |
  • 1
    WHERE parent.customer_id=1234? Where is the parent table? Nov 21 '13 at 9:04
  • I suppose it's a typo and it really means page.customer_id = 1234, at least according to the EXPLAIN.
    – jynus
    Nov 21 '13 at 10:31
  • @jynus I suppose you are right, the schema agrees with you. Nov 21 '13 at 13:42
  • @ypercube and @jynus -- sorry, that was a typo, should have been page.customer_id = 1234. Edited to correct, thanks for finding it.
    – Eric Rath
    Nov 21 '13 at 18:08

Please note that Extra: Using Index doesn't mean that an index is used- you know that because of the columns type:eq_ref and key:PRIMARY. Using index in reality means that you are using the Covering index optimisation to access data.

In your case, you are using and index for all 3 table accesses. If you wanted also the Covering index optimisation, you could create an index on (document.id, document.size), but unless that table is very large and this query is very frequent, you would not see a big difference (in cases where you are disk bound). The index on (document.size), effectively, contains the primary key, but it cannot be used, because it is hidden after the size column. In order to be used for filtering, it must in the first place. Also, in MySQL/Percona 5.5, the hidden primary key can only be used for certain sorting and covering index operations, not for filtering. This last limitation is solved in MySQL/Percona 5.6.

You biggest problem is that your query has to read 50k rows and add them (an index won't solve that). With the above tricks you can make the calculations mostly in memory (assuming a big enough buffer pool), but you still have to sum every single value. You could maintain a duplicated customer_id on document -if that makes sense (that is denormalizing, too)- to avoid 2 table accesses.

But your initial idea of denormalizing, maintaining the value precalculated -can be maintained with a trigger-, or using any other kind of cache -query cache, with all its problems, external cache like memcache, etc.- is probably the way to go if you cannot handle such latencies.

  • The problem is not reading 50k rows. But that the rows are scattered. Nov 21 '13 at 19:54
  • Yes, random reads. That was my point. Not sure if that is clear enough.
    – jynus
    Nov 22 '13 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.