I am having trouble with the following query taking quite a long time to process when results are large. The limit and offset can change as this is used with pagination. The range on capture_timestamp can also change, but in this example is finding ALL results (between 0 and 9999999999 - this field is an int of utc timestamp). The issue seems to be the ORDER BY taking up most of the processing time. It looks like it uses user_id for the table join, but then never uses anything for the ordering.
On the logs table I have the following indexes :
PRIMARY : activity_id
user_id : (user_id, capture_timestamp)
capture_timestamp : capture_timestamp (added this to see if by itself would make a difference - it did not)
There are keys setup for all the ON joins.
This particular query for example has 2440801 results (the logs table itself is currently holding 18332067 rows), but I am only showing the first 10 sorted by capture_timestamp and it takes roughly 7 seconds to return the results.
SELECT
logs.activity_id,
users.username,
computers.computer_name,
computers.os,
logs.event_title,
logs.event_target,
logs.capture_timestamp
FROM computers
INNER JOIN users
ON users.computer_id = computers.computer_id
INNER JOIN logs
ON logs.user_id = users.user_id AND logs.capture_timestamp BETWEEN :cw_date_start AND :cw_date_end
WHERE computers.account_id = :cw_account_id AND computers.status = 1
ORDER BY logs.capture_timestamp DESC
LIMIT 0,10
analyze :
Array
(
[0] => Array
(
[ANALYZE] => {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 6848.2,
"filesort": {
"sort_key": "logs.capture_timestamp desc",
"r_loops": 1,
"r_total_time_ms": 431.25,
"r_limit": 10,
"r_used_priority_queue": true,
"r_output_rows": 11,
"temporary_table": {
"table": {
"table_name": "computers",
"access_type": "ref",
"possible_keys": ["PRIMARY", "account_id_2", "account_id"],
"key": "account_id_2",
"key_length": "4",
"used_key_parts": ["account_id"],
"ref": ["const"],
"r_loops": 1,
"rows": 294,
"r_rows": 294,
"r_total_time_ms": 0.4544,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "computers.`status` = 1"
},
"table": {
"table_name": "users",
"access_type": "ref",
"possible_keys": ["PRIMARY", "unique_filter"],
"key": "unique_filter",
"key_length": "4",
"used_key_parts": ["computer_id"],
"ref": ["db.computers.computer_id"],
"r_loops": 294,
"rows": 1,
"r_rows": 3.415,
"r_total_time_ms": 0.7054,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"table": {
"table_name": "logs",
"access_type": "ref",
"possible_keys": ["user_id", "capture_timestamp"],
"key": "user_id",
"key_length": "4",
"used_key_parts": ["user_id"],
"ref": ["db.users.user_id"],
"r_loops": 1004,
"rows": 424,
"r_rows": 2431.1,
"r_total_time_ms": 4745.3,
"filtered": 100,
"r_filtered": 100,
"index_condition": "logs.capture_timestamp between '0' and '9999999999'"
}
}
}
}
}
)
)
Is there anything I can do here to speed these up? When the result set is smaller everything is pretty much immediate although I guess that is because there isn't as much sorting to do.
