We have a very big table which has around 250 million records. The table is growing bigger as every second many records are inserted. There is no update/delete operation done in this table. The table has structure similar to following sample structure
CREATE TABLE `sample_table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `BatchNo` varchar(12) DEFAULT NULL, `LotNo` varchar(12) DEFAULT NULL, `ModelNumber` varchar(20) DEFAULT NULL, `Cost` decimal(4,2) DEFAULT NULL, `QualityPercentage` tinyint(3) unsigned DEFAULT NULL, `Type` varchar(5) DEFAULT NULL, `Vendor` varchar(5) DEFAULT NULL, `Send_Time` datetime DEFAULT NULL, `InsertTime` int(10) unsigned NOT NULL, `VendorRating` tinyint(3) unsigned DEFAULT NULL, `ProductType` tinyint(4) DEFAULT NULL, `SerialNumber` varchar(4) DEFAULT NULL, `ItemID` int(11) DEFAULT NULL, `BrandID` int(11) DEFAULT NULL, `CategoryID` int(11) DEFAULT NULL, `SubTitle` varchar(10) NOT NULL DEFAULT 'gl200', `Extras` text, PRIMARY KEY (`id`), KEY `ModelNumber` (`ModelNumber`), KEY `SendTime` (`Send_Time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
The performance is slower, specially when we select to fetch records of bigger date range (Send_Time). Like if we try to fetch records from One years before to Now, it takes forever before mysql crashes.
We consulted with DBA and he said, as indexes keeps on changing every time as each minute many many records are inserted, the fetching will be slow, so he suggested -
- Don't read and write from/to same database table "sample_table".
- Create another database table, say "sample_fetch_table" with same structure as "sample_table"
- "sample_table" will only be used to insert records from now onwards.
- At night when write frequency is very low, fetch last 24 hours record from "sample_table" and insert them into "sample_fetch_table". Then delete inserted records from "sample_table".
- Now use "sample_fetch_table" table to select and display records to webpage.
- Archive X years old records to another archive table.
The common query we run is:
SELECT * FROM sample_table WHERE ( ModelNumber = "12890" AND Send_Time > FROM_UNIXTIME(1556515028) AND Send_Time < FROM_UNIXTIME(1556661540)) AND ( ( Vendor in('11','12') AND ItemID = 31 AND SubTitle LIKE 'ABC-123' ) OR ( Vendor in('ABTU','RAST','RAMT','ABRT','ABNM') AND UPPER(SubTitle) IN('RA200','PR55','XY100','TW20','D1EV','FR091','FR093','FRA12','AB23') ) ) AND BatchNo != 0 AND LotNo != 0 ORDER BY Send_time
Explain of above query
id = 1 select_type = SIMPLE table = sample_table type = range possible_keys = ModelNumber, SendTime key = SendTime key_len = 6 ref = (Null) rows = 202042 Extra = Using index condition; Using where
We are bit skeptical about this suggestion. Is this optimal solution?? Is there any other better alternatives??