1

I'm creating an order system of some kind. There is a function that I use to get orders (in PHP) and retrieve the required information about the order. However, it seems that I am pulling a lot of information, including several left joins that could be the victim of executing for a period of more than 3 seconds.

The amount of rows in each table that is used.

  • Orders - 2,485
  • Order items - 27,321
  • Order payments - 3
  • Customers - 68
  • Customers' addresses - 153 (joins twice for invoice and delivery address)
  • Order statuses - 2,277
  • Status types - 5
  • Recurring orders data - 46

I know that you will need to use EXPLAIN <query> to debug the query. It seems that it does not use any of the indexes that I have placed on the tables (basically there is an index on every column that I am joining, apart from the PRIMARY KEY columns)

This is the debug:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    2302    Using filesort
2   DERIVED     <derived3>  ALL     NULL    NULL    NULL    NULL    2302    Using temporary; Using filesort
3   DERIVED     <derived4>  ALL     NULL    NULL    NULL    NULL    2484    
3   DERIVED     o   eq_ref  PRIMARY     PRIMARY     4   oi.order_id     1   Using where
3   DERIVED     <derived6>  ALL     NULL    NULL    NULL    NULL    3   
3   DERIVED     c   eq_ref  PRIMARY     PRIMARY     4   orders_system.o.customer_id     1   
3   DERIVED     <derived7>  ALL     NULL    NULL    NULL    NULL    153     
3   DERIVED     <derived8>  ALL     NULL    NULL    NULL    NULL    153     
3   DERIVED     <derived9>  ALL     NULL    NULL    NULL    NULL    13  
3   DERIVED     <derived10>     ALL     NULL    NULL    NULL    NULL    2277    
10  DERIVED     os  ALL     NULL    NULL    NULL    NULL    2277    Using temporary; Using filesort
10  DERIVED     st  index   PRIMARY     PRIMARY     1   NULL    5   Using where; Using index; Using join buffer
9   DERIVED     recurring_orders    ALL     NULL    NULL    NULL    NULL    46  Using where; Using filesort
8   DERIVED     customer_addresses  ALL     NULL    NULL    NULL    NULL    153     
7   DERIVED     customer_addresses  ALL     NULL    NULL    NULL    NULL    153     
6   DERIVED     order_payments  ALL     NULL    NULL    NULL    NULL    3   Using where; Using temporary; Using filesort
4   DERIVED     <derived5>  ALL     NULL    NULL    NULL    NULL    2484    Using temporary; Using filesort
5   DERIVED     oi  ALL     order_id    NULL    NULL    NULL    27321   Using where; Using temporary; Using filesort
5   DERIVED     o   eq_ref  PRIMARY     PRIMARY     4   orders_system.oi.order_id   1   

Visual:

enter image description here

I am not sure why the query is executing a full table scan, this is problematic if it is taking that long to run, especially if orders are about to increase daily.

This is the query that is used to execute, it's too big to view here.

What can I do to improve this query?

  • At first glance, you appear to have a lot of functions which can negate indexes. This looks like a really juicy problem - plus your tables don't seem to be all that large. Could you zip up a dump of them and post them somewhere? I realise that you have customer data - maybe you could anonymise that? I'd love to give it a go! – Vérace Aug 16 '14 at 11:19
  • In addition, what version of MySQL are you using? With your dumps, could you also post the desired/expected result? – Vérace Aug 16 '14 at 11:26
  • Hi there, didn't know what I could use to mask the data, so I just MD5'd with random data, so here's the sql dump with DROP TABLE IF EXISTS syntax. The MySQL version is 5.5.37. – MacMac Aug 16 '14 at 12:27
  • Let me know if you need anything else for that SQL dump – MacMac Aug 16 '14 at 12:28
  • @Vérace Hey, how did you get on with this? – MacMac Aug 18 '14 at 7:30
1

At the moment, I am using an instance of MySQL 5.6.19 which has the performance_schema enabled on a 2GB x64 Linux laptop (nothing stellar by any means!). My normal response time is approx. 0.5 s - not too bad. What's your setup (RAM, disk config, OS &c.)?

I must say that I'm surprised at 3 seconds on a server. Are you sure that it's not some sort of network issue?

I checked out I/O for the query and noticed something strange - every time the query executes, the order_items table is accessed 27,321 times - which is exactly the same as the number of records. This smells of a correlated subquery - but the SQL is horribly complex and I haven't been able to get to the bottom of it (not enough time). Obviously, you should only want to access the table once (or a few times maybe) at most. Order_status is only read once for example.

It's not really a surprise that your query doesn't use indexes - see this post here which gives some explanation as to why - basically if the optimiser thinks that a FTS (full table scan) is cheaper than using the index, then that's what it will do. This is a function of the size of the table(s) (yours are v. small ===> FTS) and/or percentage of records to be retrieved (yours 100%, again ==> FTS).

I notice that you are using MyISAM tables, for full text indexes? InnoDB now has full text indexes - see here and check the docco. Also (and more importantly), InnoDB allows you to use DRI (declarative referential integrity) which is very important - IMHO, there is no good reason to use MyISAM tables anymore.

Could you explain the business logic behind this query? It strikes me (and this is only an opinion) that it is overly complex for a relatively simple system with 8 tables - has it grown over time and been added to by different people? I am respectfully suggesting that the time may have come for a complete rewrite from scratch rather than tinkering at the edges.

Another problem is that for orders, you have ordered_date as an INT(12) - when I first saw the data, I thought that it was a timestamp. You are creating extra work for yourself if you don't use the correct data type for your columns. Error checking is more difficult.

[EDIT] in response to OP's comments (easy to hard)

1)

INT v TIMESTAMP v DATE/DATETIME

Take a look at these (1, 2, 3, 4) posts. I suppose that INT v TIMESTAMP is relatively trivial (however, see point 4 in the answer here), but INT/TIMESTAMP v DATE/DATETIME isn't. Check out the answer to post 4 - you should be using DATEs/DATETIMEs when you are using INT/TIMESTAMP.

2)

MySQL 5.6.19, but the server is using 5.5.37. Would upgrading improve the performance?

Maybe, maybe not - it's probably trivial in respect to the issues that I'll be going through in point 3 - see InnoDB/Full Text Indexing. If I had to guess, upgrading would decrease performance by no more than a couple of percent (some think that the performance schema adds overhead) and quite possibly be a significant improvement. These things can vary according to many parameters (disk, CPU, RAM, network, server load, phase of the moon...). Overall though, performance should improve release by release although YMMV.

3)

General remarks.

I changed the storage engine for your system from MyISAM to InnoDB. This resulted in the query time being consistently reduced from ~ .45s to ~ .37s (~20%) - a noticeable improvement - maybe you should try it? I can only speak for the testing (not v. thorough) I did on my 2GB x64 Linux laptop. You didn't answer my question about your own server specs. If I'm getting a response time of ~ .4s, I can't for the life of me understand why you're getting 3s? Unless it's (as I suggested) a network issue, or is the server under load when you run the query?

InnoDB now has full text indexing in 5.6 (see my point 2 above) - AFAIK, that will not work in 5.5 (i.e. your table declarations will fail for InnoDB) - another reason to upgrade.

I tried to put in FOREIGN KEY relationships in the tables, but there appears to be problems with your data - I'm getting different counts after reloading the schemas (without FK and with FK). This can mean you have orphaned records, which may or may not affect the result. Anomalies occurred with orders/order_items and customers/customer_addresses. e.g.

mysql> select i.item_id, i.order_id from order_items i where i.order_id not in (SELECT ORDER_ID from orders);
+---------+----------+
| item_id | order_id |
+---------+----------+
|   26892 |     2433 |
+---------+----------+

Worse, none of the order payments appear valid

mysql> select p.order_id from order_payments p where p.order_id not in (select order_id from orders);
+----------+
| order_id |
+----------+
|     2143 |
|     2214 |
|     2216 |
+----------+
3 rows in set (0.00 sec)

mysql> 
mysql> select * from order_payments;
+------------+----------+--------+------------+------------+
| payment_id | order_id | amount | date       | is_deleted |
+------------+----------+--------+------------+------------+
|          1 |     2143 |  99.34 | 1405900800 |          0 |
|          2 |     2216 |   5.09 | 1406505600 |          0 |
|          3 |     2214 |  55.10 | 1406505600 |          0 |
+------------+----------+--------+------------+------------+

I couldn't figure out the other relationships in the time available - maybe we can go over them. It may well be that a solid DB structure with overtly declared FOREIGN KEYs may perform better. As a general rule, the more information you give the optimiser, the better.

The point about the optimiser not using indexes when tables are too small and/or too high a percentage of records is accessed still stands - however, there is no harm in doing things correctly - and as your data volumes change/grow, the optimiser may choose different plans - and the more info, the better.

The orphaned record issue (forgetting about performance for the moment) is another GOOD reason to switch to InnoDB. With proper DRI (declarative referential integrity), you cannot have orphaned records, which can be the source of tricky to find bugs.

Finally, just a few words on the system generally. It seems to me that this query is like a runaway train - it has built up and built up momentum and is now overly complex for purpose. Maybe a step back is required? I got to thinking about triggers and using them to populate other tables (similar to a materialised view) and/or even the use of views themselves. You need to have a system that you are comfortable with and that you can change to suit your needs. Some behemoth sitting in the corner spewing out answers that nobody understands is not very conducive to flexibility, upgrading or peace of mind :-).

I would like to do three things (using InnoDB)

1) get all the data types in order (DATE/DATETIMEs when appropriate)

2) sort out the foreign key relationships (need input on that - please feel free to contact me off list for this - my email is in my profile).

3) break this juggernaut down into its constituent components and explore methods of making it better, faster, easier to understand and modify.

I am unable to spend 09:00 - 18:00 at this, but would like to have a look in the evening if that suits and you are interested.

  • Thanks for taking your time to input. I agree that this query is horribly complex, but it seems that it is the only way that I can actually get the information that is needed. I am not entirely sure how to rewrite this query to improve it, because many things need to be summed (as you can see) to be accessed from above the dervied query instead of repeating it all the time which will reduce readability. It is actually growing over time, we get perhaps 10 to 20 orders a day, so the rate is increasing steadily but not as fast. – MacMac Aug 19 '14 at 8:46
  • About the ordered_date and delivery_date, I am using INT(11) for unix timestamps and I am actually using the correct type? Unless you can clarify on what you mean here. – MacMac Aug 19 '14 at 8:49
  • Also, you are using MySQL 5.6.19, but the server is using 5.5.37. Would upgrading improve the performance for this query? – MacMac Aug 19 '14 at 8:51
  • @Vérace, I applaud you for the effort in your answer and support for the community. – beeks Aug 19 '14 at 22:48

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.