Re: After partition also select is scanning all the partitions - need help
Posted by:
Venkat V
Date: September 24, 2010 11:33PM
Hi
Understood. I took the date where the partition exists and used a fresh query.
Condition of the query is mytime >= '2010-09-04 00:00:00' and mytime <= '2010-09-06 23:00:00';
in non partition table:
mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 0 |
+-------------------+-------+
1 row in set (0.00 sec)
==============
After that when i execute the query --> it took 8066 rows in set (1 min 46.29 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 8066 |
+-------------------+-------+
1 row in set (0.00 sec)
==================
But in partitioned table
mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 0 |
+-------------------+-------+
1 row in set (0.00 sec)
After that when i execute the query --> it took 8066 rows in set (0.04 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 8066 |
+-------------------+-------+
1 row in set (0.00 sec)
==========
Here i could see the difference only in speed. But still number of scan rows are same on both. I found one more weird thing too.
ysql> explain partitions select * from mytable where mytime >= '2010-09-04 00:00:00' and mytime<= '2010-09-06 23:00:00' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: PART_20100817000000,PART_20100904000000,PART_20100905000000,PART_20100906000000
type: range
possible_keys: PRIMARY,mytime
key: mytime
key_len: 8
ref: NULL
rows: 8060
Extra: Using where
1 row in set (0.00 sec)
mysql>
mysql> explain partitions select * from mytable where mytime >= '2010-09-04 01:00:00' and mytime<= '2010-09-06 23:00:00' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: PART_20100817000000,PART_20100904000000,PART_20100905000000,PART_20100906000000
type: range
possible_keys: PRIMARY,mytime
key: mytime
key_len: 8
ref: NULL
rows: 7965
Extra: Using where
1 row in set (0.00 sec)
mysql>
If we select data from > 00:00:00 also, why the partition PART_20100817000000 is selected ?. Is that the reason, we are scanning all the rows ?
The below output from non partition table in my another replicated db.
mysql> explain partitions select * from mytable where mytime >= '2010-09-04 01:00:00' and mytime <= '2010-09-06 23:00:00' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mytable
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 3067
Extra: Using where
1 row in set (0.00 sec)
mysql>
Thanks
Subject
Views
Written By
Posted
3485
September 13, 2010 08:50AM
1735
September 14, 2010 08:20PM
1904
September 16, 2010 07:06PM
1801
September 16, 2010 08:51PM
1738
September 16, 2010 09:45PM
1719
September 16, 2010 10:00PM
1722
September 21, 2010 12:17AM
1781
September 21, 2010 04:18PM
1744
September 24, 2010 06:14AM
1820
September 24, 2010 10:06PM
Re: After partition also select is scanning all the partitions - need help
2060
September 24, 2010 11:33PM
1774
September 25, 2010 01:27PM
1749
September 28, 2010 10:31AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.