There's a trick you can use, at least for date-range partitions: the HIGH_VALUE
field in user_tab_partitions
is a valid date expression. If you "eval" that, you'll get a date object for the cutoff point for that partition. You can then use that to build your partition name from.
Sample PL/SQL to try out (generates a string from the high_value
column):
declare
hv varchar2(9);
begin
for x in (select partition_name, high_value
from user_tab_partitions
where table_name = 'FOO' and partition_name not like 'PART_%')
loop
execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from dual' into hv;
dbms_output.put_line('alter table foo rename partition '||x.partition_name
||' to PART_'||hv);
end loop;
end;
/
(Replace the put_line
with execute immediate
once you've verified that it actually does what you want. The -1
in the date select is to make it more "human friendly", i.e. partition name to match the date it contains.)
As for dropping old partitions, you can use the same trick. Same type of loop to get a date object for each partition, and generate an alter table ... drop partition ...
statement for the partitions you don't want to keep.
Simplistic example where you would drop partitions older than 10 days (be vary wary of off-by-one errors, there probably is one below):
declare
dt date;
begin
for x in (select partition_name, high_value
from user_tab_partitions
where table_name = 'FOO')
loop
execute immediate 'select '||x.high_value||' from dual' into dt;
if dt < sysdate - 10 then
dbms_output.put_line('to drop: '||x.partition_name);
end if;
end loop;
end;
/
You're then left with taking care of index maintenance if necessary.
What concerns me fact that fieldC
is not the lead column in the PRIMARY KEY
.
What would be preferable is to reverse the order to the primary key columns
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`fieldC`,`id`)
-- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
partition pNULLorEMPTY VALUES IN(NULL,''),
partition pValueA VALUES IN('valueA'),
partition pValueB VALUES IN('valueB'),
partition pValueC VALUES IN('valueC')
-- other partitions
);
This may help navigate faster through the partitions. The MySQL Query Optimizer may/may not be smart enough to need the reversal.
If you have other columns in the WHERE clauses such as
WHERE fieldC='...' AND fieldA='...'
WHERE fieldC='...' AND fieldB='...'
you will need matching indexes so quickly search within one partition. Otherwise, you may find yourself doing a full table scan with the partition.
With that in mind, please create the table like this:
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`fieldC`,`id`),
KEY C_A_Index (`fieldC`,`fieldA`),
KEY C_B_Index (`fieldC`,`fieldB`)
-- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
partition pNULLorEMPTY VALUES IN(NULL,''),
partition pValueA VALUES IN('valueA'),
partition pValueB VALUES IN('valueB'),
partition pValueC VALUES IN('valueC')
-- other partitions
);
This will then accommodate such WHERE clauses.
Give it a Try !!!
CAVEAT
I noticed your character fields are CHAR
and not VARCHAR
. That's great.
If any of the character fields are VARCHAR
, please read on.
If there are any VARCHAR
fields, you need not convert them manually. Simply change the row storage format as follows:
ALTER TABLE mytable ROW_FORMAT=Fixed;
This will make the table's read speed increase 20%-25%. I wrote about this before:
Best Answer
The status of the bug is labeled as Won't fix and I can understand why in this context.
The right way would have been to run
ALTER TABLE tbl_name DROP PARTITION;
because the MySQL Documentation onALTER TABLE Partition Operations
says:Once you ran
ALTER TABLE tbl_name DISCARD TABLESPACE;
, the data dictionary should no longer be aware of the physical presence of the orphaned.ibd
files. However, since the bug does not reclaim the diskspace, perhaps the data dictionary entries for your tables is intact. Let's find out.WARNING : You should run
service mysql stop
and do a physical backup of/var/lib/mysql
First do this:
SHOW CREATE TABLE tbl_name\G
Then, run
SELECT * FROM tbl_name WHERE (stuff from partition #x) LIMIT 10;
to test the table's usability of each partition.
Once you are confortable with the table usability, you can any of the following:
REMOVE ANY PARTITION YOU WANT
If you do not trust
ALTER TABLE tbl_name DROP PARTITION partition-name;
, do it manuallyDROP ALL DATA FROM PARTITION BUT KEEP PARTITION
If you do not trust
ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;
, do it manuallyCREATE A BLANK TABLE
Give it a Try !!!
UPDATE 2013-05-20 18:41 EDT
I am glad you were able to drop the partition.
I have some distressing news: It is not possible to shrink ibdata1 without dumping all the data, deleting ibdata1, restarting mysql, and reloading.
Here are my past posts on how to do that safely:
May 21, 2012
: How large will a MySQL database be relative to the dump file?Apr 01, 2012
: Is innodb_file_per_table advisable?Mar 25, 2012
: Why does InnoDB store all databases in one file?Feb 04, 2011
: MySQL InnoDB - innodb_file_per_table cons?Oct 29, 2010
: Howto: Clean a mysql InnoDB storage engine?