If i have a database of 100 million of records and would like to do search with less time as possible!
CREATE TABLE `my_table` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT `number` BIGINT(12) NOT NULL, `name` VARCHAR(50) NOT NULL, `address` VARCHAR(150) NOT NULL, PRIMARY KEY (`id`) , UNIQUE INDEX `my_table_u` (`id` ASC, `number` ASC) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and it has data like this
(1, 981776166221,'name1','address1') (2, 499383722122,'name2','address2') (3, 983765018762,'name3','address3') (4, 986544567897,'name4','address4') (5, 990876544335,'name5','address5')
And i will only search by
number filed is arbitrary numbers consist of 12 digits
I was thinking about doing
MYSQL partitioning by doing 100 partitions, each carry records that has
number filed ends with exact two digits as follow :-
p1 (ends with 01), p2 (ends with 02), p3 (ends with 03), .., p45 (ends with 45), .., p99 (ends with 99)
so that if i’m going to search for
number = 765372819827 it might be clear it will search inside partition
27 since last two digits are
Does it really makes searching more fast ?
How can i set partitions by that way (based on only last two digits of
(The beginnings of an Answer.)
PARTITIONing is possible, but it is not likely to speed up your queries. Since converting the table is a big chore, let’s discuss other ways to improve the performance. Please provide some examples of "search" queries. From there, I may be able to advise on better indexes. If not, I will advise on how to best use Partitioning.
number = 765372819827 is the only "search", then simply
INDEX(number) solves your problem by changing the query from "check 100M rows" to "check 1 row".
And do Drop
Answered By – Rick James