Have you tried playing around with your configuration parameters,
specifically the sort buffer? I'm guessing that what is making the
query slow is not the search, but the sorting of 9k rows if I read your
results right. Easy enough to test, just drop the ORDER BY and see how
long it takes. If it's quick, then it's the sorting that is taking all
the time.
On Jul 21, 2004, at 5:30 AM, Batara Kesuma wrote:
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]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
