CODEWITHSUNDEEP
mysql
AndyH
AndyH

Reputation: 1

MySQL Problems Bigger =Slower?

Please bear with me, I'm not overly confident with MySQL coding. I am running a site that is similar to twitter. However, it seems the more followers a user gets, the slower the site runs on that particular user profile. A user with few followers has a reasonably fast page load.

Below is the "Follower" part of the MySQL, can any of you see a problem with the code or shed any light as to why this is happening?

Thanks in advance.

CODE:

CREATE TABLE IF NOT EXISTS `followers` (
    `id`        int(10)       unsigned            NOT NULL  AUTO_INCREMENT,
    `follower`  int(11)       unsigned            NOT NULL,
    `following` int(10)       unsigned            NOT NULL,
    `status`    enum('0','1') CHARACTER SET utf8  NOT NULL  DEFAULT '1' COMMENT '0 Trash, 1 Active',
    `date`      timestamp                         NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `follower` (`follower`,`following`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

Upvotes: 0

Views: 42

Answers (1)

CodeBird
CodeBird

Reputation: 3858

first thing, why are you using MyISAM engine for a table that is constantly being updated, this slows down your app as MyISAM uses table level locks, meaning selects have to wait for inserts to be done for them to run, always use InnoDB for such tables.

second thing I think it would be better to use a key for each of follower and following because now any query that is like WHERE following=X won't use your index follower

If you can show a sample of your SELECTS, maybe you'll get some more suggestions.

Upvotes: 1

Related Questions