MySQL Slow Performance Related to ci_sessions Table

codeigniter, mysql, php

Solution

Put a multi-column index on `session_id` and `user_agent`. Then it should not take any real amount of time to perform the queries, even if you have millions of rows.

Problem

I built a web app in Codeigniter that's been running fine for about a year, and suddenly the application has become extremely slow at certain times throughout the day. (sometimes 30+ seconds for page loads). I started logging and it turns out some queries are running extremely slow: ``` 9.6776 SELECT * FROM (`ci_sessions`) WHERE `session_id` = 'f2f356fb32a4e65ad3eb18f5baa74cfb' AND `user_agent` = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7; rv:11' 28.9770 UPDATE `ci_sessions` SET `last_activity` = '1335753561', `user_data` = 'a:7:{s:6:\"userID\";s:1:\"3\";s:12:\"userFullName\";s:15:\"Christian Varga\";s:5:\"admin\";s:1:\"1\";s:7:\"timeout\";s:5:\"86400\";s:12:\"lastActivity\";s:10:\"1335928202\";s:8:\"prev_url\";s:58:\"http://exampleurl.com\";s:8:\"loggedIn\";s:1:\"1\";}' WHERE `session_id` = 'f2f356fb32a4e65ad3eb18f5baa74cfb' ``` My thoughts are that there's a problem with the database server, because sometimes these exact same queries work fine, then other times throughout the day they are ridiculously slow (as above). But I'm quick to point the finger, so I just wanted to check here to see if anyone thinks it could be something wrong with the code, or if it looks like a server issue. I'd appreciate any thoughts on this. EDIT: Result of explain select: ``` id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE ci_sessions const PRIMARY,user_agent PRIMARY 122 const 1 ```

Original source