In article <[EMAIL PROTECTED]>, Batara Kesuma <[EMAIL PROTECTED]> writes:
> 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? Replacing the index on member_id by a compound index on (member_id, id) might help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
