Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What do you mean for "reindex"? On table updates also the indexes are updated. –  Emyl Oct 13 '13 at 12:57
    
for example...if I have 100 rows then I will have 100 inexes in last_active index, If I update one row then does MySQL will update whole last_activite index and or just that row entry in index? thanks –  Dshah Oct 13 '13 at 13:07
    
It doesn't rebuild the entire index, if this is what you mean. It just updates the entries related to the afftected rows. –  Emyl Oct 13 '13 at 13:21
    
MySQL uses balanced Btrees sometimes it needs to rebuild parts off that tree. But only if an value need to be updated or added in the tree.. this is also the reason why you should not create useless indexes (overindexing) because indexes slow down INSERT/UPDATE/DELETE queries. –  Raymond Nijland Oct 13 '13 at 15:45
    
Can you also post an show create table statement here? –  Raymond Nijland Oct 13 '13 at 15:48
show 2 more comments

1 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.).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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