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]
