Re: How should I partition this table?
Posted by:
Ron Bergin
Date: April 30, 2013 08:39AM
> What are the SELECTs? That PARTITIONing may not provide any performance (or other) benefits.
I'm still trying to get that info. I don't have access to the POS code so I need to wait for a response from our programmers.
> New rows are inserted based on DIVISION and DATE+TIME?
All queries and inserts are always based frst on division (store number) and then on date. The inserts would also include the time, but not the queries. Most queries are for a single date, but some are for a date range.
The table in question is the "Invoice Item Detail" so an insert would be for a single division, not all 36.
> Simply, I have not found a use case that shows any benefit. (I think "performance" is the only possible benefit in PARTITIONing.) If you can describe a use case, I am interested to hear.
Since partitioning is new to me I could be wrong, but given our division/date queries, I'd think that they would be faster with the subpartions. But, would be slower if the query only included the division and not the date. Am I wrong?
> And, with a billion rows, it would take a _long_ time to change the table.
That is a major consern of mine. It took 46 hrs to import the data, not including the time it took for the mysqldump/tar/scp/untar. I suspect that the change in partitioning would take even longer.
> With a billion rows, I suspect you have "summary table"?
I'm not sure. There are some tables that I have not determined their purpose and will need to confer with others.
I noticed in other threads that you often wanted to see the table status. You haven't asked for it here, but incase it will help, here it is.
table_name => INVITMD
Engine => InnoDB
Version => 10
Row_format => Compact
table_rows => 1025064212
Avg_row_length => 109
Data_length => 112221421568
Max_data_length =>
Index_length => 235491098624
Data_free => 53477376
Auto_increment =>
Create_time =>
Update_time =>
Check_time =>
table_collation => utf8_general_ci
Checksum =>
Create_options => partitioned
table_comment =>