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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
