trouble optimizing query - taking much longer than expected
Can't seem to figure this one out. Query is taking upwards of 7-8 seconds which is just not acceptable and seems as though it will get worse as the table(s) get larger.
Without going into a ton of detail the gist of the db setup is a hierarchy. A user account, multiple computers under that account, multiple users for each computer, then various tables linked to those users... in this case file_logs. By joining I can show the user/computer of each log under an account.
In this select I am showing the file_logs of a user account sorting by timestamp and using limit for paging. I am concerned as I use the exact same type of query (replacing file_logs with other tables) in my project. It just happens that this one is the most 'active' with data and I noticed it first.
Using MySQL 5.6 w/ innodb.
**Select :**
SELECT
users.username AS username,
computers.computer_name AS computer_name,
file_logs.file_id AS file_id,
file_logs.file_action AS file_action,
file_logs.filename AS filename,
file_logs.capture_timestamp AS capture_timestamp
FROM computers
INNER JOIN users
ON users.computer_id = computers.computer_id
INNER JOIN file_logs
ON file_logs.user_id = users.user_id AND file_logs.capture_timestamp BETWEEN 0 AND 1415301792
WHERE computers.account_id = 20 AND computers.status = 1
ORDER BY file_logs.capture_timestamp DESC
LIMIT 200000, 100
**Creates :**
CREATE TABLE `computers` (
`computer_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) unsigned NOT NULL,
`status` tinyint(1) unsigned NOT NULL,
`computer_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`capture_timestamp` int(10) unsigned NOT NULL,
PRIMARY KEY (`computer_id`),
KEY `account_id` (`account_id`,`status`),
CONSTRAINT `computers_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14028 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
CREATE TABLE `users` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`computer_id` int(10) unsigned NOT NULL,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` int(10) unsigned NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `unique_filter` (`computer_id`,`username`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`computer_id`) REFERENCES `computers` (`computer_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
CREATE TABLE `spc_file_logs` (
`file_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`file_action` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`filename` text COLLATE utf8_unicode_ci NOT NULL,
`capture_timestamp` int(10) unsigned NOT NULL,
`timestamp` int(10) unsigned NOT NULL,
PRIMARY KEY (`file_id`),
KEY `user_id` (`user_id`,`capture_timestamp`),
CONSTRAINT `file_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2985197 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
**Explain :**
Array
(
[0] => Array
(
[id] => 1
[select_type] => SIMPLE
[table] => computers
[type] => ref
[possible_keys] => PRIMARY,account_id
[key] => account_id
[key_len] => 4
[ref] => const
[rows] => 2
[Extra] => Using where; Using temporary; Using filesort
)
[1] => Array
(
[id] => 1
[select_type] => SIMPLE
[table] => users
[type] => ref
[possible_keys] => PRIMARY,unique_filter
[key] => unique_filter
[key_len] => 4
[ref] => test_inno.computers.computer_id
[rows] => 1
[Extra] => Using index
)
[2] => Array
(
[id] => 1
[select_type] => SIMPLE
[table] => file_logs
[type] => ref
[possible_keys] => user_id
[key] => user_id
[key_len] => 4
[ref] => test_inno.users.user_id
[rows] => 1770
[Extra] => Using index condition
)
)
The explain shows where the problem is, but as far as I can tell I have indexes set properly.