MySQL Forums
Forum List  »  Partitioning

select from partitioned table
Posted by: janota.m
Date: April 05, 2006 04:02AM

Hi,
I have the following problem:
I have partitioned table
CREATE TABLE tdataa_idx1_part (
tm datetime NOT NULL,
address char(6) NOT NULL,
flag char(1),
val float,
PRIMARY KEY (address,tm),
INDEX tm_addr_idx (tm,address)
) engine=myisam
PARTITION BY RANGE ( unix_timestamp(tm) ) (
PARTITION p_2006031900 VALUES LESS THAN ( unix_timestamp('2006-03-19 00:00') ),
PARTITION p_2006031901 VALUES LESS THAN ( unix_timestamp('2006-03-19 01:00') ),
PARTITION p_2006031901 VALUES LESS THAN ( unix_timestamp('2006-03-19 02:00') ),
.
.
.
PARTITION p_2006032122 VALUES LESS THAN ( unix_timestamp('2006-03-21 22:00') ),
PARTITION p_2006032123 VALUES LESS THAN ( unix_timestamp('2006-03-21 23:00') ),
PARTITION p_2006032200 VALUES LESS THAN ( unix_timestamp('2006-03-22 00:00') )
);

I inserted 48 milion rows with times between 2006-03-18 12:00 and 2006-03-21 13:00

Then I created some selects and I got unexpected results

SELECT max(tm) from tdataa_idx1_part;
RESULT: 2006-03-21 12:49:25

SELECT max(tm) from tdataa_idx1_part where tm < '2006-03-22';
RESULT: 2006-03-18 22:59:58

SELECT tm from tdataa_idx1_part order by tm desc limit 1;
RESULT: 2006-03-21 12:49:25

SELECT tm from tdataa_idx1_part where tm < '2006-03-22' order by tm desc limit 1;
RESULT: 2006-03-18 22:59:58


SELECT address,tm from tdataa_idx1_part where address='A00004' order by tm;
RESULT 29378 rows
FIRST tm= 2006-03-18 15:56:34
LAST tm = 2006-03-21 12:49:25

SELECT address,tm from tdataa_idx1_part whree address='A00004' AND tm > '2006-03-20' order by tm;
RESULT 18722 rows

SELECT address,tm from tdataa_idx1_part whree address='A00004' AND tm > '2006-03-20' order by tm desc;
RESULT empty

SELECT address,tm from tdataa_idx1_part whree address='A00004' AND tm < '2006-03-20' order by tm;
RESULT 18722 rows

SELECT address,tm from tdataa_idx1_part whree address='A00004' AND tm < '2006-03-20' order by tm desc;
RESULT empty


May I use desc sorting on partitioned table? May I use group by on partitioned table?
Thanks for any sugestions.
Martin

Options: ReplyQuote


Subject
Views
Written By
Posted
select from partitioned table
4190
April 05, 2006 04:02AM


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.