MySQL table with 100,000 records queried often

myisamMySQLmysql-5.5

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;

Best Answer

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

ALTER TABLE orders ROW_FORMAT=Fixed;

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:

Related Question