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