1

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
9
  • It's the 2nd column of the clustered key (or by first do you mean not zero?). The point was to allow select queries with a condition on user_id_to, does the PRIMARY KEY (user_id_from,user_id_to,action_type) support that?
    – Noam
    Jul 18, 2016 at 11:31
  • Oh, I read it wrong. Thought it was the 1st column. Removing comment. Jul 18, 2016 at 12:02
  • darn, I would've liked to remove the index ;) Does my primary exchange solution seem relevant?
    – Noam
    Jul 18, 2016 at 14:06
  • The idea seems good. But without more info on what queries exactly are often run, I don't think you will get anything than a general answer ("yeah this seems good"). Only proper analysis (of the workload/queries you run) and proper testing of the variations on indexing will give you a definite answer. Jul 18, 2016 at 16:19
  • 1
    Let's see the queries. You can't design indexes without having the queries in mind.
    – Rick James
    Jul 18, 2016 at 23:37

1 Answer 1

2

Indexes with the columns in the order given:

actions: INDEX(user_id_to, user_id_from) -- 'covers' first query
actions: INDEX(user_id_to, counter) -- may subsume ORDER BY and LIMIT in 3nd query

Either index will be useful for 2nd query. DROP KEY user_id_to (user_id_to) as redundant when you add my two suggestions.

For various reasons, it may be better to change to PRIMARY KEY(user_id_to, user_id_from, action_type). This would

  • replace the first index suggested above
  • mostly eliminate the need for the second index above, thereby speeding up inserts (by having fewer indexes)
  • If you need user_id_from in the WHERE, but without user_id_to, then you would need some index starting with user_id_from. (I don't yet see a SELECT that begs for such.)

In summary (for actions indexes):

PRIMARY KEY(user_id_to, user_id_from, action_type)
INDEX(user_id_from, ...) -- if needed.

"action_type" -- Do you really need 4 bytes for it? How many distinct values are there? If very few, consider an ENUM, otherwise a TINYINT UNSIGNED or SMALLINT UNSIGNED (1,1,2 bytes, respectively). Shrinking 4 bytes to 1 would save 8GB (3 bytes * 2 copies (data+secondary key) * 1.3B rows).

There is a perfectly nice 2-byte countryCode; don't use a separate Id:

countryCode CHAR(2) CHARACTER SET ascii

How many "locations" are there? If not millions, I would not bother normalizing city, state, country. If there are a lot of locations rows in each city, then consider normalizing only city, then have the state and country spelled out in the city table. (I oppose "over-normalizing".)

If users has PRIMARY KEY(user_id), I have no further suggestion for indexing that table.

The 3rd query says LEFT JOIN users and users.something.... You probably did not want LEFT.

4
  • Very helpful, 10x!. Would you say inserting will be much easier when primary starts with user_id_to considering each insert is 1.5k rows that all share the same user_id_to ?
    – Noam
    Jul 20, 2016 at 20:27
  • Not necessarily. Keep in mind that any secondary indexes need updating, too.
    – Rick James
    Jul 20, 2016 at 20:54
  • But doesn't MySQL organize data on disk according to primary key?
    – Noam
    Jul 20, 2016 at 20:58
  • 1
    Yes, but... The PRIMARY KEY and the data are organized that way in one BTree. Each secondary key is in another BTree, organized according to that index. So, the change of PK made the data faster to store, but the secondary key(s) slower.
    – Rick James
    Jul 20, 2016 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.