Mysql – Database peformance optmization

MySQL

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 –

  1. Don't read and write from/to same database table "sample_table".
  2. Create another database table, say "sample_fetch_table" with same structure as "sample_table"
  3. "sample_table" will only be used to insert records from now onwards.
  4. 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".
  5. Now use "sample_fetch_table" table to select and display records to webpage.
  6. 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 > 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??

Best Answer

Partitioning would be a Good Thing, given the size of table and its rate of growth.

That said, there are a couple of things you could try to improve the query before doing that.

AND SubTitle LIKE 'ABC-123'

You're not doing any wild-card searching, so the "like" operator is unnecessary; a simple "=" would do. But your DBMS might well be able to work that out for itself.

AND UPPER(SubTitle) IN('RA200', ...

Now there's a problem.
Unless you have function-based indexes then the database has to scan every record, pass the SubTitle field through the Upper() function and then test the result of that calculation against the values in the "in" clause. Scanning every record is what we call Table Scanning and, on a table this size, it's going to be slow!

You need to fix the data in that field so that it's consistently in upper case and remove the Upper() function call from the query.

... as indexes keeps on changing every time as each minute many many records are inserted, the fetching will be slow ...

If you're only adding new records, the index will be extended frequently, but index records that have already been written will be largely unchanged so, unless you're always looking for the most recent records, this shouldn't have that big an impact.

I'd expect the Table Scanning problem to hit you far harder than that.

The performance is slower, especially 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.

And what are you going to do with a whole year's worth of data having retrieved it? if you're going to do any kind of aggregation, look at pushing that back onto the database.

Related Question