Mysql – Fast query in MySQL with table of 1B+ rows

MySQLmysql-5.7

Consider a MySQL table with the following columns:

source_id integer
timestep integer
position integer
value float

This table contains slightly more than 1,000,000,000 rows. The data is read-only and serves as source for certain analyses.

What can be done to speed up the queries?

I've created indices on all four columns. What else can be done?
The DB is currently on a MBP mid 2015, but it is going to be migrated to a workstation. Any suggestions on RAM-wise and / or SSD-wise?

Consider the following view on the previous table and a query on the view:

create view V(id, block, value) as
select source_id, round(timestep/25), avg(value) 
from T 
group by source_id, round(timestep/25);

select * from V where id=1;

The last query is taking ages to complete.

Version is 5.7.15

Best Answer

I would suggest that the round(timestep/25) is what is causing the slowdown in the group by part of the view.

Could you add a further field (timestep_aggregate) to the table containing the results of the round function? Using a trigger to populate the field on insert / update.

Or if using MySQL 5.7.5 or above, you could use a generated column which would do this for you - see Generated Columns in MySQL 5.7.5

If so, you could then create a combined index on source_id and timestep_aggregate, then use the new field instead of the function within your view. This would be much more performant.

Related Solutions

Mysql – Use MySQL to regularly do multi-way joins on 100+ GB tables

Have you tried piling more data and benchmarking it? 100K rows is inconsequential. Try 250M or 500M like you're expecting you'll need to handle and see where the bottlenecks are.

An RDBMS can do a lot of things if you pay careful attention to the limitations and try and work with the strengths of the system. They're exceptionally good at some things, and terrible at others, so you will need to experiment to be sure it's the right fit.

For some batch processing jobs, you really cannot beat flat files, loading the data into RAM, smashing it around using a series of loops and temporary variables, and dumping out the results. MySQL will never, ever be able to match that sort of speed, but if tuned properly and used correctly it can come within an order of magnitude.

What you'll want to do is investigate how your data can be partitioned. Do you have one big set of data with too much in the way of cross-links to be able to split it up, or are there natural places to partition it? If you can partition it you won't have one table with a whole pile of rows, but potentially many significantly smaller ones. Smaller tables, with much smaller indexes, tend to perform better.

From a hardware perspective, you'll need to test to see how your platform performs. Sometimes memory is essential. Other times it's disk I/O. It really depends on what you're doing with the data. You'll need to pay close attention to your CPU usage and look for high levels of IO wait to know where the problem lies.

Whenever possible, split your data across multiple systems. You can use MySQL Cluster if you're feeling brave, or simply spin up many independent instances of MySQL where each stores an arbitrary portion of the complete data set using some partitioning scheme that makes sense.

MySQL Rows to Columns to HBase (using Sqoop)

If you are interested in returning all the values as columns, you need to be try something very adventurous. First look at your query

SELECT 
    CONCAT(id, '-', date),
    MAX(IF(`num` = 0, avg, NULL)) num0 
FROM table 
GROUP BY 
    id, 
    date;

If would be a big mess to get MySQL to execute it as a query by giving MySQL the query with each column formulated.

Perhaps you can get MySQL to concatenate the column values using GROUP_CONCAT. That function was designed for aggregation (or aggravation if you are the actual developer). You can take all the num values can display it as a column-separated listed of numbers like this:

SELECT 
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0))) numlist
FROM table 
GROUP BY 
    id, 
    date;

You can also change the list to be delimited by pipes instead of commas like this:

SELECT 
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0)) SEPARATOR '|') numlist
FROM table 
GROUP BY 
    id, 
    date;

The default maximum length of a GROUP_CONCAT is 1024.

You need to change that max length in the session using this:

SET group_concat_max_len = 10240;

before you issue your query.

Best Answer

Related Solutions

Mysql – Use MySQL to regularly do multi-way joins on 100+ GB tables

MySQL Rows to Columns to HBase (using Sqoop)

Give it a Try !!!

Related Question