2

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

  • Can you add examples of the most common queries you run against this table? And the EXPLAIN plan of the specific query that takes long time? – ypercubeᵀᴹ Apr 29 '19 at 6:20
  • @ypercubeᵀᴹ, I have update the Original Post – WatsMyName Apr 29 '19 at 6:46
  • Optimization possibilities: 1) Divide OR on two separate queries combined with UNION 2) Normalize SubTitle by converting the data to uppercase for to avoid the function in the condition 3) Replace LIKE without pattern to equal sign = 4) Create proper composite index (something like ModelNumber, Vendor, SubTitle, [ItemID,] Send_Time, must be checked for each separate query type) 5) Find the possibility to decrease the amount of output records and, maybe, to avoid field IN (list). – Akina Apr 29 '19 at 8:21
  • @WatsMyName how can you have a key on ModelNumber when you do NOT have a column named ModelNumber? Try using real information for your questions, rather than edited that is error prone. – Wilson Hauck Apr 29 '19 at 18:50
  • @WilsonHauck, There is ModelNumber Column, typo error, i have fixed that – WatsMyName Apr 30 '19 at 3:19
1

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.

  • Thanks for the answer, in regard to partition, in which column will it be good to partition?? I tried doing it on Send_Time but got error A PRIMARY KEY must include all columns in the table's partitioning function. Please note that Send_Time cannot be unique. – WatsMyName Apr 30 '19 at 9:36
1

Considering the query and the table size, this looks like a good candidate for partitioning on the Send_Time column.

  • Partitioning on Send_Time column in my test database, partition didn't work got A PRIMARY KEY must include all columns in the table's partitioning function error. Send_Time cannot be added as Primary key as Send_Time cannot be unique. – WatsMyName Apr 30 '19 at 9:29
  • @WatsMyName The problem is caused by the fact that you have a key defined for ModelNumber that does not contain Send_Time. To solve this, you have to add the Send_Time column to the ModelNumber key. You can check MySQL documentation for more info. – Alexandru Cojocaru Apr 30 '19 at 10:59
  • So you meant Remove Send_Time as a key and add it with ModelNumber?? – WatsMyName May 2 '19 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.