November 22nd, 2013, 05:35 PM
Speeding up MySQL
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.
I feel like a simple count should not take 18 minutes. I'm looking for articles / suggestions on ways to improve performance.
mysql> SELECT COUNT(1) FROM my_table;
| COUNT(1) |
| 36389161 |
1 row in set (18 min 9.19 sec)
November 22nd, 2013, 06:15 PM
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"?
November 22nd, 2013, 09:10 PM
Thank you for your reply:
I'm looking up some mysql slow count and trying to run an optimize table on it to see if it helps.
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