Re: would like best table/index structure advice?
Sorry for the confusion, I may have submitted different versions of the table, since I was messing with it so much.
Rick, it could be that the long queries occur while the delete is happening. When this locking occurs it only happens to a few of the clients, not all of them. For example, I can get locked out for 100+ sec, meanwhile, the person sitting next to me can access the data immediately.
Here is the info you asked for:
1. show create table session:
session | CREATE TABLE `session` (
`ctr` int(10) unsigned NOT NULL auto_increment,
`session_id` varchar(40) NOT NULL,
`ip_address` varchar(16) NOT NULL,
`user_agent` varchar(50) NOT NULL,
`referrer_user_id` int(10) unsigned default NULL,
`referrer_addr` varchar(100) default NULL,
`last_activity` int(10) unsigned NOT NULL,
`session_data` text NOT NULL,
PRIMARY KEY (`ctr`),
UNIQUE KEY `idx_session_id` (`session_id`),
KEY `idx_selects` (`session_id`,`user_agent`,`last_activity`)
) ENGINE=InnoDB AUTO_INCREMENT=24402 DEFAULT CHARSET=latin1 |
2. slow select:
# Query_time: 85 Lock_time: 0 Rows_sent: 1 Rows_examined: 18370
SELECT *
FROM session
WHERE session_id = '06a05c42d9a58515d2bac592c3bdfc8b'
AND last_activity > 1234638681
AND user_agent = 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv';
3. slow update:
# Query_time: 128 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE session SET last_activity = 1235848241, user_agent = 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US
; rv', ip_address = '69.15.182.225', referrer_addr = '', session_data = 'a:2:{s:11:\"url_history\";a:5:{i:0;s:3
5:\"/support/view/highestSupportAmount/\";i:1;s:37:\"/support/view/highestSupportAmount/72\";i:2;s:37:\"/suppor
t/view/highestSupportAmount/36\";i:3;s:40:\"/support/design/38/skulls-are-still-kool\";i:4;s:34:\"/support/view
/highestSupportAmount\";}s:16:\"referrer_user_id\";N;}' WHERE session_id = 'a73bc2035d2ca7a58844e4ec52e48c3a';
4. slow delete:
# Query_time: 94 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
DELETE FROM session WHERE last_activity < 1235606350;
The thing is that when a slow delete occurs, the slow select/update don't necessarily occur around that time frame, maybe 16 minutes after on a non-consistent basis.