How to Accelerate Queries on Big Tables
Tables that have rows more than 100M in numbers may have a performance degradation on queries, especially when the queries must join multiple tables, which… Read More »How to Accelerate Queries on Big Tables
Partitions
Partition Keys Can Make Unique Indexes Useless
Normally, we build an unique index for protecting the table from inserting duplicate values of combined columns. If the constraint of the unique index is… Read More »Partition Keys Can Make Unique Indexes Useless
How to Use String-Typed Columns as Partition Keys
You might have known that LIST, RANGE, and HASH partitions all refuse to accept VARACHAR column as the native partition key.mysql> alter table locations partition… Read More »How to Use String-Typed Columns as Partition Keys
How to Resolve ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
ERROR 1217 (23000) Got a MySQL ERROR 1217 (23000) when tried to partition a table. mysql> alter table geolocations partition by ... ... ERROR 1217… Read More »How to Resolve ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Partition Key is NOT Necessarily the First Key of Composite Indexes
If you do believe that the arrangement of positions in a composite index really matters on the whole performance, here is another evidence for that.MySQL… Read More »Partition Key is NOT Necessarily the First Key of Composite Indexes
How to Use a Timestamp Column as a Partition Key
Using a date or int column as a partition key is a lot easier than using a timestamp column in MySQL. Because the final datetime… Read More »How to Use a Timestamp Column as a Partition Key
How to Resolve ERROR 1486 (HY000) in MySQL
When we partitioned a table by a timestamp-typed column "order_time" in table "orders", it generated an error 1486 like this:mysql> ALTER TABLE orders PARTITION BY… Read More »How to Resolve ERROR 1486 (HY000) in MySQL