We have a very large table that contains about 250 million records. The table grows every second when many records are inserted. No update / delete operation is performed in this table. The table has a structure similar to the 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, `ModeNumber` 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 & # 39; gl200 & # 39; The text `Extras`, PRIMARY KEY (`id`), KEY `ModelNumber` (` ModelNumber`), KEY `SendTime` (` Send_Time`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8
Performance is slower, especially when we select records from a longer date range (Send_Time). As if we were trying to recover records of One years before to Now, it takes forever before the mysql crash.
We consulted DBA and he said, as indexes change every minute, many records are inserted, loading will be slow, he suggested –
- Do not read and write from / in the same "sample_table" table.
- Create another database table, for example "sample_fetch_table" with the same structure as "sample_table"
- "sample_table" will only be used to insert records from now.
- At night, when the writing frequency is very low, get the record of the last 24 hours of "sample_table" and insert them in "sample_fetch_table". Then delete the inserted records from "sample_table".
- Now use the "sample_fetch_table" array to select and display records on a web page.
- Archive X records in another archive table.
The current query that we execute is:
SELECT * FROM sample_table WHERE (ModelNumber = "12890" AND Send_Time> FROM_UNIXTIME (1556515028) AND Send_Time <FROM_UNIXTIME (1556661540) AND ((Seller in (& # 39; 11 & # 39;, & # 39; 12 & # 39;) AND ItemID = 31 AND LIKE subtitle - ABC-123 & # 39;) OR (Vendor in (& # 39; ABTU & quot ;, & quot; RAST & # 39 ;, & quot; RAMT & quot ;, & quot; ABRT & # 39 ;, & quot; ABNM & # 39;) AND UPPER (subtitle) IN ('RA200', 'PR55', 'XY100', 'TW20', 'D1EV &' # 39; FR091 & # 39; & # 39; FR091 & # 39; FRA12 & quot ;, & quot; AB23 & quot;)))) AND BatchNo! = 0 AND LotNo! = 0 ORDER BY Send_time
Explain the query above
id = 1 select_type = SIMPLE table = sample_table type = beach possible_keys = ModelNumber, SendTime key = SendTime key_len = 6 ref = (Null) rows = 202042 Extra = Use of the index condition; Using where
We are a bit skeptical about this suggestion. Is this optimal solution ?? Are there other alternatives? ??