I have a single database of about 100 tables to store various kinds of information.

The most important table is our order table which is used to store customers orders and is over 100000 records as of now and growing.

This table is the most queried table in our database, for various parts of information needed from real time order dashboards, statistics, analytics etc.

I monitor the database on a regular basis and have slow queries enabled on the database to track issues.

I use scripts like mysqltuner to spit out query on a daily basis.

I also use mysqlsla to gather info about the top 10 slowest queries in our database.

sample stat
Count         : 11.48k  (30.66%)
Time          : 19.623758 s total, 1.709 ms avg, 239 µs to 2.475017 s max  (18.64%)
  95% of Time : 5.246833 s total, 481 µs avg, 239 µs to 1.095 ms max
Lock Time (s) : 14.460071 s total, 1.259 ms avg, 53 µs to 2.462555 s max  (41.38%)
  95% of Lock : 806.43 ms total, 74 µs avg, 53 µs to 137 µs max
Rows sent     : 1 avg, 0 to 9 max  (0.99%)
Rows examined : 6 avg, 1 to 28 max  (0.15%)

Most of the slowest queried involve the order table mentioned above. I use MyISAM as my storage engine so possible issues could be:

  1. Table locking
  2. Indexing issues

How could I improve these stats, I have indexing in place for these tables and have kept tweaking them to improve read queries.

Table schema

`orderid` int(11) NOT NULL AUTO_INCREMENT,
`cityid` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `model_type` tinyint(1) unsigned DEFAULT '1',
`userid` int(11) DEFAULT NULL,
`usertype` char(1) DEFAULT NULL,
`time` time DEFAULT NULL,
`ordercode` char(8) DEFAULT NULL,
`restid` smallint(3) unsigned NOT NULL,
`areaid` smallint(3) unsigned DEFAULT NULL,
`restname` varchar(50) DEFAULT NULL,
`date` date NOT NULL,
`del_time` time NOT NULL,
`status` tinyint(3) unsigned NOT NULL,
`amount` float NOT NULL,
`deliverycharge` smallint(4) unsigned DEFAULT '0',
`tax` float NOT NULL,
`total` float NOT NULL,
`extras` varchar(255) DEFAULT NULL,
`requests` varchar(255) DEFAULT NULL,
`discount` float DEFAULT NULL,
`rdiscount` float DEFAULT NULL,
`reason` varchar(255) DEFAULT NULL,
`rest_order` tinyint(1) unsigned DEFAULT NULL,
`admin_user` varchar(25) DEFAULT NULL,
`mode` char(1) NOT NULL,
`priority_order` tinyint(1) unsigned DEFAULT '0',
`payment_mode` tinyint(1) unsigned DEFAULT '0',
`km` tinyint(3) unsigned DEFAULT NULL,
`order_type` tinyint(1) NOT NULL DEFAULT '1',
`coupon_discount` smallint(3) DEFAULT '0',
`pickup_time` time NOT NULL,
PRIMARY KEY (`orderid`),
KEY `cityid` (`cityid`),
KEY `date_3` (`date`,`status`,`mode`),
KEY `orderid` (`orderid`),
KEY `time` (`time`),
KEY `userid` (`userid`,`usertype`),
KEY `restid` (`restid`,`date`,`status`)

slow log query

SELECT `a`.`orderid`, `a`.`date`, `a`.`status`, `a`.`restname`, `a`.`admin_user`, `a`.`model_type`, `b`.`name` as cityname
FROM `tk_order_queue` AS a
INNER JOIN `tk_cities` AS b ON `a`.`cityid` = `b`.`id`
WHERE `a`.`date` =  '2012-06-30'
AND `a`.`status` =  0
AND `a`.`mode` =  1
ORDER BY `a`.`orderid` desc;
  • Please run SHOW CREATE TABLE orders\G and post that in the question Commented Jul 6, 2012 at 13:37
  • 2
    Am I reading that incorrectly, or is the average query time 1.7ms? Why on earth would you look to speed that up?
    – Philᵀᴹ
    Commented Jul 6, 2012 at 13:48
  • I am a little confused as to why does that appear in the slow query logs.
    – sheldon
    Commented Jul 6, 2012 at 14:40
  • @RolandoMySQLDBA i have attached the schema
    – sheldon
    Commented Jul 6, 2012 at 14:49
  • 1
    Just because you have indexes on the table doesn't mean they are the right indexes for your queries. Could you post a few examples of slow queries and their EXPLAIN output? Also, how often is the orders table updated? Commented Jul 6, 2012 at 19:25

1 Answer 1


You will have to compare the WHERE clauses and GROUP BY and ORDER BY statements of all your queries to make sure your current indexes can support them in their EXPLAIN plans.

Yesterday, I answered this question : InnoDB vs MyISAM with many indexes

In that question I suggested doing something to the MyISAM table that you can do as well


This will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. Your table will bloat to the maximum size for the row layout times the number of rows. You table could double or triple in size.

Where is the benefit? Your MyISAM table will then be read from/written to anywhere from 20% - 30% faster without changing anything else.

I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

  • Thanks for the detail explanation, yes the indexes added to the table are based on the queries used for the selects, group by and order by statements used in our system. Have been monitoring the queries without indexes logs and been updating the tables accordingly.
    – sheldon
    Commented Jul 6, 2012 at 15:31

Your Answer

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

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