Search code examples
mysqlauto-incrementpartitioning

mysql like button table id auto_increment optimization

I have this table:

CREATE TABLE IF NOT EXISTS `likes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(40) NOT NULL,
  `post_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

it is for a big store, it allow customers to like products. it record who like (user) and what (post id). like facebook.

eg: 1 - maria - 233 2 - john - 527 ...

so, 1 like, 1 record.

How can i optimize it? to allow many records? and to be as fast as possible?

can i use KEY Partitioning? any example of that? what about id int(11) is it too small?

thank you a lot!

Solution

  • INT, by default is SIGNED; the range is -2 billion to +2 billion. It occupies 4 bytes. But AUTO_INCREMENTs are never negative, so INT UNSIGNED would be better -- range 0 to + 4 billion (and still 4 bytes).

    4 billion is more than half the population of the world. If you come anywhere near that number of "likes", someone is cheating and you should plug that hole.

    Ditto for post_id. And user_id. (Oops, I am getting ahead of myself.)

    user VARCHAR(40) CHARACTER SET latin1 -- If you are hoping to get even remotely close to 4 billion, you need to let the Chinese, Thai, Koreans, etc, in. Use utf8mb4.

    Do you require users to register? (Otherwise how can you prevent someone from "stuffing the ballot box"?) You have another table called Users? And it has an AUTO_INCREMENT? And that id is a 4-byte INT UNSIGNED? So, don't use the full name here; use that id. Smaller --> more cacheable --> less I/O --> faster.

    PARTITION? No. Don't use PARTITIONing without first understanding what little it can do and how much it cannot do. It does not inherently provide speed; certainly not for this use case.

    ENGINE=MyISAM. Have you ever had the power fail on server with a billion-row MyISAM table? When the server back up it starts complaining about a corrupt table. You spend a few hours figuring out what it means. Then you start the REPAIR TABLE. Hours later that finishes. Meanwhile the system is unavailable. Use ENGINE=InnoDB.