Mysql category query

Question

I have a query which gets rows by category. A sample query is

Ex. Select * from db where category = 14

Anybody have an idea or suggestions on how to optimize this so that the query can run faster?

Answer 1

Well you have a pretty simple query there as it is. You might be able to speed it up by selecting less fields, do you really need all the fields in the row, or just certain ones. You can also ensure you have an index on the category field.

Answer 2

The most important thing to do is make sure you've got an index on the cateogry column. Run the query through EXPLAIN to see if it's using a query:

root@localhost:temp> explain select * from db where category = 14 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: db
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where

"Using where" means it's doing a full table scan, wherein it compares the value of the category column in every row to 14. If there's an index in place, mysql can jump directly to the appropriate rows.

root@localhost:temp> alter table db add index c1 (category);
root@localhost:temp> explain select * from db where category = 14\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: db
         type: system
possible_keys: category
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 

Note the "possible_keys" field, and that "Extra" is empty.

Indexes are extremely important; a schema without indexes is either very specialized or only half-done. You should look at all the things mysql with indexes to really get a feel for how important they are.