MySQL table with 100,000 records queried often

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:

• Table locking

• 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` varch

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:

• Which is faster, InnoDB or MyISAM? (May 03, 2012)

• Which mysql storage engine to choose? (May 02, 2012)

• Best of MyISAM and InnoDB (Sep 20, 2011)

• Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)

• What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)

StackExchange Database Administrators Q#20493, answer score: 8