Reducing execution time for the following query

How can I reduce time for the following query?

SELECT *
FROM (`ci_session`)
WHERE `session_id` = '8011e978d8692846a22bc8f7f806af4d'
AND `user_agent` = 'Mozilla/5.0 (Windows NT 6.1; rv:2.0) Gecko/2010010' 

This query takes around 0.3 seconds to execute on my MySQL 5.5开发者_开发知识库

This is an very important query as it determines the session of the user (PHP/CodeIgniter)

The table contains 1000 rows.

Couple of easy wins to start with...

1. Select specific columns, not *

2. Add an index for the session_id and user_agent columns

session_id is a fixed with column, try to change it to CHAR(32) instead of VARCHAR if that's the case.

And make a UNIQUE INDEX for session_id.

CREATE UNIQUE INDEX idx_session ON ci_session (session_id)

Or an INDEX with prefix

CREATE INDEX idx_session ON ci_session (session_id(8))

and change the length of the prefix to see which one is faster.

Make sure your ci_session table has an index on session_id (or that the latter is the primary key). There isn't much more that you can do to make it faster.

Do you need to select *?

If you just need the username, only select that. Then add a composite index to the table:

user_agent, session_id, username

By including username in the index, it should be able to return the query directly from the index without looking up the row in the table.

Also, do you really need to filter on user_agent? Is the session_id not unique?

Integer matching is always faster than string matching (and will almost always have lower storage requirements) so try using integer session IDs instead of the strings you're using now.

Shorter strings are faster to match than longer strings, and fixed width strings are also a bit better than variable length ones (varchars) so you might consider applying a hashing function to the user agent and storing it in a fixed-width char field. Then match the MD5 of the agent string against the MD5 of the stored one.

Last, but by no means least, indexing can provide a massive speedup for queries, so consider what your indexes are and add appropriate ones if an index seems like it would speed the query up. Using EXPLAIN SELECT will help you determine where queries can be sped up with indexes.