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 and number filed is arbitrary numbers consist of 12 digits XXXXXXXXXXXX.
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 27.
Does it really makes searching more fast ?
How can i set partitions by that way (based on only last two digits of number filed)?
(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.
If 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 my_table_u.