Hi, I use InnoDB for my table, and I think I have hit the point where I can't do query optimization any more.
The query is as simple as below: SELECT SQL_NO_CACHE diary_id, LEFT(body, 28) AS body , id FROM diary_comment WHERE member_id='343' ORDER BY id DESC LIMIT 15; And it took about 5 seconds. # Query_time: 5 Lock_time: 0 Rows_sent: 15 Rows_examined: 9088 The table structure: CREATE TABLE `diary_comment` ( `id` int(10) unsigned NOT NULL auto_increment, `diary_id` int(10) unsigned NOT NULL default '0', `member_id` mediumint(8) unsigned NOT NULL default '0', `sender_id` mediumint(8) unsigned NOT NULL default '0', `date` date NOT NULL default '0000-00-00', `time` time NOT NULL default '00:00:00', `body` text, PRIMARY KEY (`id`), KEY `diary_id` (`diary_id`), KEY `member_id` (`member_id`), KEY `sender_id` (`sender_id`) ) TYPE=InnoDB The table has about 4 million rows and I have index on member_id. The table is growing at the rate of 1% a day. I am thinking about splitting the table into several tables, so every table contains diary_comments for limited member_id, and I can split the tables into several machines. But the downside is I can't use auto_increment anymore. Anyone has any ideas or suggestions about this? I also read about MySQL Cluster, and I wonder if this can speed things up. Regards, Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
