2
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?

4 Answers 4

3

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 !!!

1
  • I think i have that index already. and it is innodb. innodb_buffer_pool_size is 8Mb, recommended is 990M..... I have 512M on the server only..
    – joetsuihk
    Commented May 5, 2011 at 5:19
2

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.
1
  • the query is as simple as a select and one where codition and one order by. Index is being used but still a load of 19k rows? will check cache hit ratio, thx
    – joetsuihk
    Commented May 4, 2011 at 11:33
0

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

0

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

1
  • sorry, added the index table
    – joetsuihk
    Commented May 5, 2011 at 5:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .