Database peformance optmization 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 


Problem

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.


UPDATE

The common query we run is: 

SELECT * 
FROM sample_table 
WHERE (    ModelNumber = "12890" 
       AND Send_Time &gt; FROM_UNIXTIME(1556515028) AND Send_Time &lt; 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??
Considering the query and the table size, this looks like a good candidate for partitioning on the Send_Time column.