mysql – Optimizing database performance

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 

Problem

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 –

  1. Do not read and write from / in the same "sample_table" table.
  2. Create another database table, for example "sample_fetch_table" with the same structure as "sample_table"
  3. "sample_table" will only be used to insert records from now.
  4. 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".
  5. Now use the "sample_fetch_table" array to select and display records on a web page.
  6. Archive X records in another archive table.

UPDATE

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? ??