Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
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?

share|improve this question

4 Answers 4

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.
share|improve this answer
    
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 May 4 '11 at 11:33

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

share|improve this answer
    
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 May 5 '11 at 5:19

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

share|improve this answer

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

share|improve this answer
    
sorry, added the index table –  joetsuihk May 5 '11 at 5:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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