How to reduce mysql disk io?

Question

mysql> explain SELECT *
    -> FROM (`items`)
    -> WHERE `states` = 'A'
    -> ORDER BY `updated_date` desc
    -> LIMIT 20520, 40;
+----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+
| id | select_type | table  | type   | possible_keys              | key                 | key_len | ref      | rows  | Extra       |
+----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+
|  1 | SIMPLE      | items  | ref    | states_updated_date,states | states_updated_date | 6       | const    | 19040 | Using where |
+----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+


    mysql> describe items;
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| url                 | varchar(255) | NO   | UNI | NULL    |                |
| vendor              | varchar(255) | NO   |     | NULL    |                |
| title               | varchar(255) | NO   |     | NULL    |                |
| price               | int(11)      | YES  |     | NULL    |                |
| contact_seller_link | varchar(255) | NO   |     | NULL    |                |
| seller              | varchar(255) | NO   |     | NULL    |                |
| query_count         | varchar(7)   | NO   |     | NULL    |                |
| warrenty            | varchar(63)  | NO   |     | NULL    |                |
| created_date        | varchar(63)  | NO   |     | NULL    |                |
| notes_count         | varchar(7)   | NO   |     | NULL    |                |
| views_count         | varchar(7)   | NO   |     | NULL    |                |
| effective_date      | varchar(63)  | NO   |     | NULL    |                |
| updated_date        | varchar(63)  | NO   | MUL | NULL    |                |
| images              | text         | NO   |     | NULL    |                |
| description         | text         | NO   |     | NULL    |                |
| extra_comment       | text         | NO   |     | NULL    |                |
| queries             | text         | NO   |     | NULL    |                |
| crawl_date          | datetime     | NO   |     | NULL    |                |
| brand               | varchar(63)  | NO   | MUL | NULL    |                |
| states              | varchar(1)   | YES  | MUL | NULL    |                |
| focal_length_id     | int(9)       | YES  | MUL | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------+

mysql> show index from items;;
+-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name               | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| items |          0 | PRIMARY                |            1 | id              | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          0 | url                    |            1 | url             | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | focal_length_id        |            1 | focal_length_id | A         |         669 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | focal_length_id_states |            1 | focal_length_id | A         |         308 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | focal_length_id_states |            2 | states          | A         |         604 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | brand                  |            1 | brand           | A         |          26 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | updated_date           |            1 | updated_date    | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | states_updated_date    |            1 | states          | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |
| items |          1 | states_updated_date    |            2 | updated_date    | A         |      124498 |     NULL | NULL   |      | BTREE      |         |
| items |          1 | states                 |            1 | states          | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.13 sec)

it is working on 19k rows per query, data set around 40k. I can improve it by adding index? or increase the ram used my mysql?

Answer 1

The MySQL load for SELECTs should be CPU bound not I/O bound. If it is I/O bound, then you should:

• add indexes where appropriate
• optimize queries
• increase the memory used to cache the tables and the queries - check the cache hit ratio. MySQL Tuner can help you in this task.

Answer 2

The index you need to make is given in the WHERE and ORDER BY clauses, states and updated_date

ALTER TABLE items ADD INDEX states_updated_date_ndx (states,updated_date);

Rerun your EXPLAIN plan afterwards.

You can also reduce the size of the columns by running PROCEDURE ANALYSE() like this:

SELECT states,updated_date FROM items PROCEDURE ANALYSE();

This will not show all the rows. It will simply pass through all the rows and determine the optimal data types for both the states and date_updated columns.

If the table items is MyISAM, you will have to increase key_buffer_size. If InnoDB, increase innodb_buffer_pool_size. At the very least, make sure all caches are already big enough.

Give it a Try !!!

Answer 3

You could reduce the size of your index by changing the states column to CHAR(1). Smaller indexes fit into memory better.

Answer 4

Don't even think of performance without indices or keys.