Hello.
> I have a query that is taking days to complete (not good). If I change Really, not good. What does SHOW PROCESSLIST report about the thread of this query? David Sparks <[EMAIL PROTECTED]> wrote: > I have a query that is taking days to complete (not good). If I change > the query so that it selects less rows it runs fast. > > I ran an explain on both queries and it didn't give any hints as to why > the one query is taking days to run. In fact explain knows how many > rows each query will examine. > > Please help explain this behavior to me. > > Thanks, > > ds > > > > The output of running the queries: > > mysql> select count(*) from msgs where message_id > 1120000000 and > message_id < 1120000001; > +----------+ > | count(*) | > +----------+ > | 6 | > +----------+ > 1 row in set (0.00 sec) > > > mysql> select count(*) from msgs where message_id > 1120000000 and > message_id < 1120000111; > (running for 2 days now) > --------->%------------- > > The output of explain on both queries: > > mysql> explain select count(*) from msgs where message_id > 1120000000 > and message_id < 1120000111\G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: msgs > type: range > possible_keys: PRIMARY > key: PRIMARY > key_len: 8 > ref: NULL > rows: 580 > Extra: Using where; Using index > 1 row in set (0.00 sec) > > mysql> explain select count(*) from msgs where message_id > 1120000000 > and message_id < 1120000001\G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: msgs > type: range > possible_keys: PRIMARY > key: PRIMARY > key_len: 8 > ref: NULL > rows: 5 > Extra: Using where; Using index > 1 row in set (0.00 sec) > --------->%------------ > > > The table description: > > mysql> describe messages\G > *************************** 1. row *************************** > Field: message_id > Type: double(15,5) unsigned > Null: > Key: PRI > Default: 0.00000 > Extra: > *************************** 2. row *************************** > Field: abc1 > Type: int(10) unsigned > Null: > Key: > Default: 0 > Extra: > *************************** 3. row *************************** > Field: r_datetime > Type: datetime > Null: YES > Key: > Default: 0000-00-00 00:00:00 > Extra: > *************************** 4. row *************************** > Field: abc2 > Type: int(10) unsigned > Null: YES > Key: MUL > Default: 0 > Extra: > *************************** 5. row *************************** > Field: abc3 > Type: int(10) unsigned > Null: YES > Key: MUL > Default: 0 > Extra: > *************************** 6. row *************************** > Field: abc4 > Type: varchar(255) > Null: YES > Key: > Default: > Extra: > *************************** 7. row *************************** > Field: abc5 > Type: float > Null: YES > Key: MUL > Default: 0 > Extra: > *************************** 8. row *************************** > Field: abc6 > Type: int(10) unsigned > Null: > Key: MUL > Default: 0 > Extra: > *************************** 9. row *************************** > Field: abc7 > Type: int(10) unsigned > Null: YES > Key: MUL > Default: 0 > Extra: > *************************** 10. row *************************** > Field: abc8 > Type: int(10) unsigned > Null: YES > Key: MUL > Default: 0 > Extra: > *************************** 11. row *************************** > Field: abc9 > Type: int(10) unsigned > Null: YES > Key: MUL > Default: 0 > Extra: > *************************** 12. row *************************** > Field: abc10 > Type: int(10) unsigned > Null: > Key: > Default: 0 > Extra: > *************************** 13. row *************************** > Field: abc11 > Type: int(10) unsigned > Null: > Key: > Default: 0 > Extra: > 13 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/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
