Dan Buettner wrote: > bowen - > > Right now, it appears your performance hinges on I/O to the disk drive. > > The reason you are seeing fast performance when querying against the > primary key(SELECT COUNT(*)) is it is only reading from the index, > which is probably all in memory. When you do a SELECT * even when > against an indexed field, it has to access the table data, not just > the index, so it hits the disk drive. MyISAM tables always have that row count available hence the reason why SELECT COUNT(*) FROM table is always fast.
Regards --jm > > There are a couple of things you can do to improve speed, probably a > fair amount in this case: > 1 - increase amount of RAM in machine. This will allow more data to > be cached in memory, for faster access. When MySQL first starts up, > performance may be slow, as it reads from disk to fill the caches, but > then will be fast going forward. > 2 - install some sort of striped disk storage system to allow faster > access to data on disk. Not as fast as RAM but still a big boost, and > performance will be more consistent. > > I'd recommend doing both if possible. > > Dan > > On 10/9/06, bowen <[EMAIL PROTECTED]> wrote: >> How to speed up query of indexed column with 5M rows? >> >> I have a table with more than 5M rows. (400M .MYD 430M .MYI). >> >> It took 27 seconds to do a common select...where... in the index column. >> >> I can not bear the long run. >> >> Vmstat show that system was bounded by IO busy.(Always more than 13000 >> bi/s, blocks input / second .) >> >> I increase some options to speed up query. >> I try "SET GLOBAL key_buffer_size=256*1024*1024", the query still need >> 14 seconds. >> try "read_buffer_size = 2M" again, down to 9s. >> >> >> It is a great improvement, but the result still can not be acceptable, >> no matter to multiple query. >> >> >> Is there any method to speed up the query. I found the select by >> primary key of the 5M Row is very quickly(0.05s). >> >> I found if it is IO busy, process will hang much time for loading. If >> index is cached in memory, process will be much more faster. >> >> Bottleneck is IO performance. How to improve mysql io performance? >> >> >> >> Mysql 4.1.21 + linux 2.6.17 + 512M memory + IDE Disk udma5 >> >> ----------------------- >> CREATE TABLE /*!32312 IF NOT EXISTS*/ `flow` ( >> `id` bigint(20) unsigned NOT NULL auto_increment, >> `name` varchar(255) NOT NULL default '', >> `owner` int(10) NOT NULL default '0', >> `uuid` varchar(36) NOT NULL default '', >> `length` int(11) default '0', >> `time` int(11) default '0', >> PRIMARY KEY (`id`), >> UNIQUE KEY `id` (`id`), >> UNIQUE KEY `flow_uuidindex` (`uuid`), >> KEY `flow_nameindex` (`name`), >> KEY `flow_fk_owner` (`owner`) >> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; >> >> >> mysql> explain select * from flow where owner=11251; >> +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ >> >> | id | select_type | table | type | possible_keys | key | key_len | >> ref | rows | Extra | >> +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ >> >> | 1 | SIMPLE | flow | ALL | NULL | NULL | NULL | NULL | 5122593 | >> Using where | >> +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ >> >> 1 row in set (0.00 sec) >> >> mysql> explain select * from flow where owner=11251; >> Empty set (18.82 sec) >> >> >> If there is no method to improve, can you advise me a redesign of table? >> I think there is a way to do if I only use unique key in where. >> >> Thanks >> >> >> Owner index was 253285 unique owner, probably means user. On average, >> one user have 20 flows. >> mysql> select count(*) from owner; >> +----------+ >> | count(*) | >> +----------+ >> | 253285 | >> +----------+ >> 1 row in set (0.00 sec) >> >> Sorry for misspell of gid. It is uuid of the flow. I think I should >> redesign the table. But how to solve the huge one-multiple >> relationship. >> >> The flow table is the only largest table in the database, the others >> are relative small just like the owner table size. >> >> >> I have a design． Create an additinal field in owner table to store the >> pk(or uuid) of flows owned by this user. Than use a store procedure to >> update this field called by changing to the flow table. But I think it >> is a bad design, right ? And also mysql 4.1.x do not support store >> procedure. I do not want to migrate the whole database, and I am sure >> there are some incompatible. >> >> Thanks... >> > -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include <std/disclaimer.h> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
