CODEWITHSUNDEEP
phpmysqldatabase-design
Kiritonito
Kiritonito

Reputation: 424

Store user comments in database

I'm facing some performance issues with MySql. The query to select the comments related to the specific url id took about 1.5 ~ 2 seconds to complete.

Comments Table

CREATE TABLE `comments` (
    `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `url_id`            INT UNSIGNED NOT NULL,
    `user_id`           INT UNSIGNED NOT NULL,
    `published`         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `votes_up`          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `votes_down`        SMALLINT UNSIGNED NULL DEFAULT 0,
    `text`              TEXT,

    PRIMARY KEY (id),
    INDEX (url_id),
    INDEX (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have inserted 100.000 comments, and executed this query: SELECT * FROM comments WHERE url_id = 33 ORDER BY published ASC LIMIT 0,5.

Is this normal? A simple query taking almost 2 seconds to complete? Should I create a separate table just for the comment's text?

Youtube, Facebook and so on has millions (or billions) of comments, how they get the comments for that object (video, post, etc) so fast?

To resume my question:

  1. I stop worrying about performance and stick with this and when the website reaches certain amount of user activity, I start worrying about this.
  2. If I need to worry about this, what's wrong to my table structure? What I need to change to reduce the completion time of that query?

Update

The explain output:

+----+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table    | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
+----+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | comments | ref  | url_id        | url_id   | 4       | const |  549 | Using where; Using filesort |
+----+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------------+

Upvotes: 1

Views: 1465

Answers (1)

e4c5
e4c5

Reputation: 53734

The problem here is that mysql uses only one index per table. That's why your index on published wasn't used. Your explain shows that it's using the index to identify what rows to return, that leaves the RDBMS unable to use an index for the sorting.

What you should do is to create a composite index on (user_id,published)

Upvotes: 3

Related Questions