List:General Discussion« Previous MessageNext Message »
From:Jacques Marneweck Date:October 9 2006 1:18pm
Subject:Re: How to speed up query of indexed column with 5M rows?
View as plain text  
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 <linuzboy@stripped> 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>

Thread
How to speed up query of indexed column with 5M rows?bowen9 Oct
  • Re: How to speed up query of indexed column with 5M rows?Dan Buettner9 Oct
    • Re: How to speed up query of indexed column with 5M rows?Jacques Marneweck9 Oct
      • Re: How to speed up query of indexed column with 5M rows?Rolando Edwards9 Oct
  • Re: How to speed up query of indexed column with 5M rows?Brent Baisley9 Oct
    • Re: How to speed up query of indexed column with 5M rows?Rolando Edwards9 Oct
Re: How to speed up query of indexed column with 5M rows?bowen10 Oct