0

I have important data that consists of approx 25 million rows and will grow year on year. This dates back to 1995 and is split into yearly CSV files.

Example: 1995.csv, 1996.csv and so on up to 2016.csv.

The data is of UK addresses with the Postcode being the primary identifier.

Let's say I perform a search on SW19 2EA.

I need the database to query the data and return the result set that is available for that given postcode. This will return data that contains information such as the address, postcode, property type and so on.

The problem I have at the moment is querying 25 million rows in a single table is fairly heavy and has a hefty delay. That being said, I'm only just learning advanced SQL and my configuration is probably not up to scratch.

We have 2x 128 GB RAM servers both running dual Xeon x5's on 10 Gbit dedicated connections. We assume the severs are of no issue whatsoever, and therefore are unsure what the best solution would be.

Do we store all the databases into a single table and do some research on the best configurations or do we store them in an object database?

The data will not change apart from new data being added in 2017 that is for the 2017 year. All other years are static and are never changed.

My initial thought was to store each data set in its own table according to each CSV file, so, for example, 1 table for 1995 data, 1 table for 1996 data and so on. The question is, whenever a search query is performed, is it possible to have multiple workers query each table at the same time, so that, let's say, 10 tables are being queried concurrently as opposed to one giant 25-million-row table?

Could you use the same use case on an object database? I'm also curious if it's possible to cache the entire database, it being static data. On the other hand, would this significantly reduce the query delay of around 20 seconds that I currently experience on the single 25-million-row table?

Example of CSV data structure:

"3E0330EF-67CA-8D89-E050-A8C062052140","112000","2006-05-22 00:00","MK13 7QS","F","N","L","HOME RIDINGS HOUSE","13","FLINTERGILL COURT","HEELANDS","MILTON KEYNES","MILTON KEYNES","MILTON KEYNES","A","A"

I have been using Laravel with MariaDB backend and been importing the CSV files into MariaDB. Then I have been simply using a query like this:

Select * from history where `postcode` = 'MK13 7QS';

and in Laravel terms:

$postcode = 'MK13 7QS';
History::where('postcode', '=', $postcode)->get();

Database Layout is not perfectly set up and the data types can certainly be tweaked to make them more ideal for the dataset.

Currently I have just used varchar on every column apart from the date column, as I have only been playing about with things to try and get a more suitable setup and configuration working for production.

I'm hoping to get some much appreciated advice from a newcomer point of view.

  • What is the structure of the .csv file? – Vérace Nov 22 '16 at 2:55
  • Verace, Sorry i did mean to add that but got carried away on the typing!! Will add that now to the OP, Thanks. – Birdy Nov 22 '16 at 3:04
  • Please provide SHOW CREATE TABLE and the SELECT you would like to perform. From those, I can advise. ENGINE=CSV is not efficient for queries. – Rick James Nov 22 '16 at 3:14
  • @RickJames sorry for the lack of information, I have just updated the information again to try and build a better picture, Hope that helps. – Birdy Nov 22 '16 at 3:22
  • VARCHAR() is the right thing to use for postcode. However, if you have problems with inconsistent inclusion of spaces, you should remove all spaces as you INSERT. (A one-time use of REPLACE(.., ' ', '') would change the existing rows.) – Rick James Nov 22 '16 at 19:06
2

You will keep the data 'forever', correct? That is, there is no 'purging'.

Is there potentially a different entry for each year? Or multiple entries for each year?

Why not have a single, non-partitioned, InnoDB, table with the PRIMARY KEY starting with the postcode. Perhaps you want the address and DATETIME as other columns in the PRIMARY KEY.

PRIMARY KEY(postcode, address, as_of)

That would let you get an ordered history of who lived at $pc, $addr:

SELECT * FROM tbl
    WHERE postcode = $pc
      AND address = $addr
    ORDER BY as_of;

It would be very fast (much less than 1 second), regardless of hardware. (With ENGINE=CSV, you are looking at minutes or hours for any query.)

If you envision other queries, let's see them.

  • Rick i am very greatfull that you have taken the time out your day/evening to help me out, Its very appreciated. In terms of the order by date, The date does not matter in terms of the query however i do return the date set for the record when i return the dataset so forexample i will display the address, postcode, date and price paid for the property. My inital thoughts was to have some kind of method inplace that goes from the first two or three charectors in the query so MK13 postcode would make the database only query MK13 data and not any other letters such as AB1 or BD2 ect ect – Birdy Nov 22 '16 at 3:27
  • .. if that makes sense? And yeah the data is kept forever, No updates are needed, We only add new data at the end of each year, There can be multiple records for a single postcode/property however each record has a uuid forexample: 3E0330EF-67CA-8D89-E050-A8C062052140 (Sorry ran out of space on first comment!) – Birdy Nov 22 '16 at 3:28
  • BTree indexes are efficient enough. -- Playing tricks with the first few characters is not worth the effort. – Rick James Nov 22 '16 at 18:46
  • Rick, How could i use the postcode as the primary key if there are multiple records with the same postcode and address? Two use cases would be that the multiple houses on the same street contain the same postcode and the other use case is that the database contains multiple records for a given address over the days, months and years, So going back to your answer i think i would have to have the postcode, address and property sale date as the primary key(s), I'm very intrigued to achieve what you said with "It would be very fast (much less than 1 second), regardless of hardware." Thanks – Birdy Nov 22 '16 at 22:42
  • 1
    I suggested a single, 3-column ("composite"), PK. I assumed that combination to be always unique. If that is wrong, then I will give you another technique. – Rick James Nov 22 '16 at 22:51

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.