Hi Ganbold, It's taking 35 seconds because it has to look at 2000+ [large] rows in the data file to get the value of "size." If the index is just on uid, remove it and make a composite index on (uid, size):
ALTER TABLE message DROP INDEX uid, ADD INDEX uid_size (uid, size); 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? > Egor, > > Result of explain: > > mysql> explain select sum(size) from message where uid='2945'; > +---------+------+---------------+------+---------+-------+------+------ -------+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +---------+------+---------------+------+---------+-------+------+------ -------+ > | message | ref | uid | uid | 4 | const | 2491 | Using > where | > +---------+------+---------------+------+---------+-------+------+------ -------+ > > Ganbold > > > At 12:54 PM 17.10.2003 +0300, you wrote: > >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: > > > > > > select sum(size) from message where uid='2945'; > > > > > > > > ---------------------------------------------------------------------- ------------------------------------------------------------------------ --- > > > mysql> select sum(size) from message where uid='2945'; > > > +-----------+ > > > | sum(size) | > > > +-----------+ > > > | 249722888 | > > > +-----------+ > > > 1 row in set (35.04 sec) > > > > > ---------------------------------------------------------------------- ------------------------------------------------------------------------ --- > > > > > > This query took me 35 seconds. Message table has 286867 rows and 4.7GB > > of size. > > > > > > > > > uid field is indexed as well as some others. > > > >uid is a part of index. Is uid the first part of compound index? > >Use EXPLAIN to see if MySQL uses index. > > > > > > > > Table structure: > > > > > ---------------------------------------------------------------------- ------------------------------------------------------------------------ --- > > > mysql> describe message; > > > > > +-------+---------------------------+---------+------+-----------+------ ----------+ > > > | Field | Type | Null | Key | Default | > > Extra | > > > > > +-------+---------------------------+---------+------+-----------+------ ----------+ > > > | sjt | tinytext | YES | | > > NULL | | > > > | uid | int(1) unsigned | | MUL | > > 0 | | > > > | mto | varchar(255) | YES | MUL | NULL | | > > > | mfr | varchar(255) | YES | MUL | NULL | | > > > | msg | longtext | YES | | > > NULL | | > > > | date | int(1) | YES | MUL | NULL | | > > > | dir | char(1) | YES | MUL | NULL | | > > > | stat | enum('N','O','S','D') | YES | MUL | NULL | | > > > | id | int(1) unsigned | | PRI | NULL | > > auto_increment | > > > | size | int(1) | YES | MUL | > > NULL | | > > > > > +-------+--------------------------+---------+-------+-----------+------ ----------+ > > > 10 rows in set (0.00 sec) > > > > > ---------------------------------------------------------------------- ------------------------------------------------------------------------ --- > > > > > > > > > > >-- > >For technical support contracts, goto https://order.mysql.com/?ref=ensita > >This email is sponsored by Ensita.net http://www.ensita.net/ > > __ ___ ___ ____ __ > > / |/ /_ __/ __/ __ \/ / Egor Egorov > > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > >/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > > <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]