Skip navigation links

MySQL Forums :: Optimizer & Parser :: How to speed up query of indexed column with 5M rows?


Advanced Search

How to speed up query of indexed column with 5M rows?
Posted by: ryan zheng ()
Date: October 09, 2006 06:34AM

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 a 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...

Options: ReplyQuote


Subject Views Written By Posted
How to speed up query of indexed column with 5M rows? 21832 ryan zheng 10/09/2006 06:34AM
Re: How to speed up query of indexed column with 5M rows? 8351 Larry Kagan 10/09/2006 08:11AM
Re: How to speed up query of indexed column with 5M rows? 6840 ryan zheng 10/10/2006 12:23AM
Re: How to speed up query of indexed column with 5M rows? 6177 Larry Kagan 10/10/2006 08:45AM
Re: How to speed up query of indexed column with 5M rows? 6030 Björn Steinbrink 10/12/2006 07:57PM
Re: How to speed up query of indexed column with 5M rows? 5862 Toa Sty 10/09/2006 09:40AM
Re: How to speed up query of indexed column with 5M rows? 5478 ryan zheng 10/09/2006 08:39PM
Re: How to speed up query of indexed column with 5M rows? 4536 KimSeong Loh 10/09/2006 07:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.