Re: How to speed up query of indexed column with 5M rows?

2006-10-09 Thread bowen
+---++-+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-

Re: How to speed up query of indexed column with 5M rows?

2006-10-09 Thread Rolando Edwards
the dataset, then MySQL has it right in this case. - Original Message - From: Brent Baisley <[EMAIL PROTECTED]> To: bowen <[EMAIL PROTECTED]>, mysql@lists.mysql.com Sent: Monday, October 9, 2006 10:52:47 AM GMT-0500 US/Eastern Subject: Re: How to speed up query of indexed colu

Re: How to speed up query of indexed column with 5M rows?

2006-10-09 Thread Brent Baisley
of transferring the results. - Original Message - From: "bowen" <[EMAIL PROTECTED]> To: Sent: Monday, October 09, 2006 8:54 AM Subject: How to speed up query of indexed column with 5M rows? How to speed up query of indexed column with 5M rows? I have a table with more than

Re: How to speed up query of indexed column with 5M rows?

2006-10-09 Thread Rolando Edwards
: Monday, October 9, 2006 9:18:01 AM GMT-0500 US/Eastern Subject: Re: How to speed up query of indexed column with 5M rows? 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 query

Re: How to speed up query of indexed column with 5M rows?

2006-10-09 Thread Jacques Marneweck
. > > 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 ind

Re: How to speed up query of indexed column with 5M rows?

2006-10-09 Thread Dan Buettner
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 col

How to speed up query of indexed column with 5M rows?

2006-10-09 Thread bowen
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

Re: How to speed up query?

2003-10-17 Thread Matt W
It should then be very fast because it doesn't need to go to the data file -- as EXPLAIN will show with "Using index." Hope that helps. Matt - Original Message - From: "Ganbold" Sent: Friday, October 17, 2003 4:14 AM Subject: Re: How to speed up query?

Re: How to speed up query?

2003-10-17 Thread Ganbold
Egor, Result of explain: mysql> explain select sum(size) from message where uid='2945'; +-+--+---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-

Re: How to speed up query?

2003-10-17 Thread Egor Egorov
Ganbold <[EMAIL PROTECTED]> wrote: > > I'm having some trouble running one query. I'm using FreeBSD 4.8 with > linuxthread enabled mysql-4.0.14. > Server has 1GB ram and SCSI hard disk. > > I need to get size of email message which is stored in MyISAM table. > > The problematic query is: > > s

Re: How to speed up query?

2003-10-16 Thread Kelley Lingerfelt
I think, if I understand this right, that the problem is the int(1) which limits the index to only the first digit, try to change that to int(11) or something like that, and see if that cures it. Kelley Ganbold wrote: > Hi, > > I'm having some trouble running one query. I'm using FreeBSD 4.8 wi

How to speed up query?

2003-10-16 Thread Ganbold
Hi, I'm having some trouble running one query. I'm using FreeBSD 4.8 with linuxthread enabled mysql-4.0.14. Server has 1GB ram and SCSI hard disk. I need to get size of email message which is stored in MyISAM table. The problematic query is: select sum(size) from message where uid='2945'; ---