mysql partition on categorical fields and timestamp column which is varchar

Question

Currently we have table:

CREATE TABLE `T_TRANS` (
  `CASE_ID` varchar(20) DEFAULT NULL,
  `C_ID` varchar(20) DEFAULT NULL,
  `C_ST_IND` smallint(6) DEFAULT NULL,
  `D_DTTM` int(11) DEFAULT NULL,
  `E_ID` varchar(10) DEFAULT NULL,
  `E_LONG` decimal(11,7) DEFAULT NULL,
  `E_LAT` decimal(9,7) DEFAULT NULL,
  `EV_IND` smallint(6) DEFAULT NULL,
  `H_B_IND` smallint(6) DEFAULT NULL,
  `V_IND` varchar(15) DEFAULT NULL,
  `I_IND` smallint(6) DEFAULT NULL,
  `I_P_IND` smallint(6) DEFAULT NULL,
  `I_S_IND` smallint(6) DEFAULT NULL,
  `IS_D_IND` smallint(6) DEFAULT NULL,
  `IS_R_IND` smallint(6) DEFAULT NULL,
  `L_IND` smallint(6) DEFAULT NULL,
  `D_LONG` decimal(11,7) DEFAULT NULL,
  `D_LAT` decimal(9,7) DEFAULT NULL,
  `L_P_C_DTTM` int(11) DEFAULT NULL,
  `L_T_E_DTTM` int(11) DEFAULT NULL,
  `M_IND` varchar(20) DEFAULT NULL,
  `N_D_COUNTER` smallint(6) DEFAULT NULL,
  `O_ID` smallint(6) NOT NULL,
  `P_ID` varchar(50) DEFAULT NULL,
  `R_E_IND` smallint(6) DEFAULT NULL,
  `R_IND` smallint(6) DEFAULT NULL,
  `S_C_DTTM` varchar(20) DEFAULT NULL,
  `S_IND` smallint(6) DEFAULT NULL,
  `T_T_RED` varchar(20) DEFAULT NULL,
  `U_D` int(11) DEFAULT NULL,
  `V_D` int(11) DEFAULT NULL,
  `CRT_USR_NAM` varchar(45) DEFAULT NULL,
  `CRT_DTTM` varchar(45) DEFAULT NULL,
  `UPD_USR_NAM` varchar(45) DEFAULT NULL,
  `UPD_DTTM` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My where query will be on the following columns for a specific or combination of values

C_ST_IND values range from (0,1,2,3,4,5,6,7,8,9,10,11,12)
E_IND values range from (0,1,2,3,4,5,6,7)
R_IND Values range from (0,1)
R_E_IND Values range from (0,1)
L_IND Values range from (0,1)
IS_D_IND Values range from (0,1)
I_S_IND Values range from (0,1)
I_P_IND Values range from (0,1)
I_IND Values range from (0,1)
S_IND Values range from (0,1,2,3)
H_B_IND Values range from (0,1)
O_ID Values range from (1,2,3,4,5,6)

Also my date columns are in varchar with format - '2019-01-25 01:01:59' CRT_DTTM and UPD_DTTM

On average - Daily Load will be

CRT_DTTM    Count
2019-01-20  656601
2019-01-21  686018
2019-01-22  668486
2019-01-23  680922
2019-01-24  693700

This table has millions of records now and currently in production- without any partition and index.

It is taking lot of time - to run any query.

Now, i need to create partitions/Index. Tried partition on a existing table , it takes forerver to run.

What is the best partition methods for above listed columns (frequently used in where clause) and for date columns(CRT_DTTM and UPD_DTTM) for Year, Month, Week and Day Partition. Also any indexes?

This table will hold Three Years of data. Right now we have 3 Months of data. How do i move my current table to a new partitioned table. I am new to mysql, any information would help reduce production query run time and report generation.

Partitioning will not help you (it's not a tool to speed up queries), but indexes will. Which index(es) you need will depend on your queries, so please add one or two representative queries (there is usually no index for every use query/use case, so start with 1). Relevant aspects: Are your columns used with `=` or with `in`/a range? Is there a special data distribution? (Maybe (some) columns have 1% "1" and 99% "0", and you usually only need the "1"-rows). Which/how many columns are (usually) included in your queries (E.g. you might usually include a daterange, maybe just a single day.) — Solarflare, Jan 25 '19 at 15:44
You might like my presentation [How to Design Indexes, Really](https://www.slideshare.net/billkarwin/how-to-design-indexes-really) or the [video](https://www.youtube.com/watch?v=ELR7-RdU9XU). — Bill Karwin, Jan 25 '19 at 20:37
Your table doesn't even have a PRIMARY KEY! See https://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key — Bill Karwin, Jan 25 '19 at 20:37

score 0 · Answer 1 · answered Jan 26 '19 at 06:40

PARTITIONs do not intrinsically provide any performance. Let's see the queries so we can judge whether you have one of the rare cases, such as purging 'old' data.

Suggest you shrink the data -- SMALLINT takes 2 bytes; TINYINT UNSIGNED takes 1 byte and can easily hold all those small values you mention. 7 decimal places for lat/lng gives you the precision of under 16mm or less than one inch. Do you need that much precision? Consider DECIMAL(8,6) for latitude and (9,6) for longitude; that will save 3 bytes for each pair. (Hmmm.. Why are there two pairs?)

"A long time to run 'any' query"? Let's see some of them and work on optimizing them. The usual problem is that you need to touch lots of rows. Shrinking the rows (as mentioned above) will help some. But the big improvement comes with not touching as many rows.

This smells like a Data Warehouse application? If so, perhaps building and maintaining Summary tables is the way to go. See http://mysql.rjweb.org/doc.php/summarytables . Show me some more info, and I will help you.

Do you intend to purge data after 3 years? If so, I recommend partitioning by month and have 38 partitions. Details here: http://mysql.rjweb.org/doc.php/partitionmaint . With that, the 680K-row nightly DELETE becomes a much quicker DROP PARTITION. (Meanwhile, there is probably no benefit to the performance of queries.)

My Index Cookbook: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

mysql partition on categorical fields and timestamp column which is varchar

1 Answers1