I have the following table:
CREATE TABLE `actions` (
`user_id_from` int(11) unsigned NOT NULL,
`user_id_to` int(11) unsigned NOT NULL,
`action_type` int(11) NOT NULL,
`counter` smallint(11) unsigned NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`user_id_from`,`user_id_to`,`action_type`),
KEY `user_id_to` (`user_id_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Currently it has around 1.3 Billion Rows, Data size is 109GB and Index Size is 44.5GB.
The INSERT queries on this table come in chunks of ~1k rows all with the same user_id_to. Most of the SELECTS have user_id_to in the WHERE condition, user_id_from is also used but less frequently, and results are mostly sorted by counter DESC and grouped by user_id_from.
Currently both INSERTs and SELECTs are running much slower than I'd expect. I have a theory a possible improvement will be to replace between the two indexes and making the new indexes:
PRIMARY KEY (,`user_id_to`, `user_id_from`,`action_type`),
KEY `user_id_from` (`user_id_from`)
The hope is that all writes will happen in the same area on the disk, and index changes therefore will also be minimal, and same thing for fetching the data when having one specific user_id_to as a WHERE condition, all data will be in the same place on disk, so it will run much faster.
Does my theory hold water? Any other suggestion?
UPDATE: My Queries:
SELECT COUNT( DISTINCT user_id_from) FROM actions WHERE user_id_to=:user_id
SELECT locations_users.*, location_country, countryCode, location_state, location_city
FROM actions INNER JOIN locations_users ON actions.user_id_from = locations_users.user_id
INNER JOIN countries ON locations_users.countryId = countries.countryId
LEFT JOIN states ON locations_users.stateId = states.stateId
LEFT JOIN cities ON locations_users.cityId = cities.cityId
WHERE actions.user_id_to = :user_id
SELECT actions.counter, actions.user_id_from, actions.action_type
FROM actions
LEFT JOIN users ON actions.user_id_from=users.user_id
WHERE actions.user_id_to=:user_id AND users.something>50
ORDER BY actions.counter DESC LIMIT 40
PRIMARY KEY (user_id_from,user_id_to,action_type)
support that?