#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2005
    Posts
    628
    Rep Power
    143

    Speeding up MySQL


    Hi guys,

    I realize this is a fairly open ended topic but I'm going to ask anyway.

    I have a MySQL database that is about 200gb in size. I do not feel this database is optimized very well.

    For example:

    Code:
    mysql> SELECT COUNT(1) FROM my_table;
    +----------+
    | COUNT(1) |
    +----------+
    | 36389161 |
    +----------+
    1 row in set (18 min 9.19 sec)
    I feel like a simple count should not take 18 minutes. I'm looking for articles / suggestions on ways to improve performance.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,644
    Rep Power
    1945
    How does you table looks like (SHOW CREATE TABLE).

    While you wait for an answer this place, have you tried to search for "mysql slow count"?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2005
    Posts
    628
    Rep Power
    143
    Thank you for your reply:

    Code:
    CREATE TABLE `my_table` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `type` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
      `data` varchar(270) COLLATE utf8_unicode_ci DEFAULT NULL,
      `hash_value` binary(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `hash_value` (`hash_value`),
      KEY `data` (`article_id`(255))
    ) ENGINE=InnoDB AUTO_INCREMENT=37857236 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    I'm looking up some mysql slow count and trying to run an optimize table on it to see if it helps.

IMN logo majestic logo threadwatch logo seochat tools logo