I have a users table with various fields which are getting updated frequently.
i.e table have:
userid - PRIMARY int
profile_views - int
ratings - int
last_active - int ( to store UNIX timestamps for sorting purpose)
I also have an index on last_active
to get the last active users. When user logins or performs a certain action last_active
gets updated and same with profile_views
and ratings
.
My question is does mysql reindex all table ro reindex index last_active when I update last_active index value or update profile_views or ratings?
table:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip_address` varbinary(16) NOT NULL,
`username` varchar(100) NOT NULL,
`password` varchar(80) NOT NULL,
`salt` varchar(40) DEFAULT NULL,
`email` varchar(100) NOT NULL,
`title` varchar(255) NOT NULL,
`detail` varchar(500) NOT NULL,
`genre_id` int(2) NOT NULL,
`category` tinyint(1) NOT NULL,
`profile_views` int(11) NOT NULL,
`ratings ` int(11) NOT NULL,
`last_active` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `genre_id` (`genre_id`),
KEY `last_active` (`last_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4059 ;
and second table to join to
CREATE TABLE IF NOT EXISTS `users_genre` (
`userid` int(11) NOT NULL,
`genre_id` int(11) NOT NULL,
KEY `userid` (`userid`),
KEY `genre_id` (`genre_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Thanks
Best Answer
MySQL will not serve you stale data (if that is your question). The index is maintained and kept up to date as part of every operation (whether be INSERT, UPDATE, DELETE etc.).