Ideal configuration and structure for single db + single table + 20 million static records

Question

I've spent the last few weeks trying to learn SQL the best I can and after doing research and reading many articles, specifically (This SO Thread), I have ran out of luck and was hoping to get some better advice to achieve the result I need.

---

My setup:

1. Single Database (Running MariaDB, Though this could be changed)
2. Single Table with 20+ Million records that has static data and is never changed. (This is also saved to a csv file)
3. Windows server 2012 datacenter (64gb ram + Xeon E5 @ 2.4ghz with 12 cores & 24 logical processors) - (Could change OS if recommended)
4. I have been using the latest Xampp/phpmyadmin at the moment while testing different methods (but plan to use a more enterprise solution like sqlserver).

---

What I need to achieve:

I need to be able to run a query (with the least delay as possible) against the data either it be to the database/table or to the csv file directly, and return a dataset containing 4 of the column values.

---

My data:

The data is historic property purchase information that dates back to 1999. A property can be bought and sold numerous times so the table has duplicated records for the (postcode, address and date), however the uuid of every row is unique.

Example Query:

To run query's I have been using laravel / php as I am not too advanced with mysql command line and would go something like:

$query = DB::table('postcode_records')->select('uuid', 'postcode', 'address', 'sale_date')->where('postcode', '=', $pcode)->get();

This will query the table and return all the records for the given postcode. This works fine however it takes 3-4 minutes to return the result and would be horrendous when in production. After reading many articles I'm hoping to achieve a much faster response time of ms or 1/2 seconds if possible.

---

My Structure:

(1) - This is the current structure with multiple composite keys..

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| uuid               | varchar(250) | NO   | PRI | NULL    |       |
| sale_price         | int(10)      | NO   |     | NULL    |       |
| sale_date          | datetime     | NO   | PRI | NULL    |       |
| postcode           | varchar(15)  | NO   | PRI | NULL    |       |
| house_name_num     | varchar(50)  | NO   |     | NULL    |       |
| flat_or_apartment  | varchar(50)  | NO   |     | NULL    |       |
| street             | varchar(50)  | NO   |     | NULL    |       |
| town               | varchar(150) | NO   |     | NULL    |       |
| address            | varchar(150) | NO   |     | NULL    |       |
| city               | varchar(150) | NO   |     | NULL    |       |
| district           | varchar(152) | NO   |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

(2) - I have tried this structure with a single primary key however no difference in query time.

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| uuid               | varchar(250) | NO   | PRI | NULL    |       |
| sale_price         | int(10)      | NO   |     | NULL    |       |
| sale_date          | datetime     | NO   |     | NULL    |       |
| postcode           | varchar(15)  | NO   |     | NULL    |       |
| house_name_num     | varchar(50)  | NO   |     | NULL    |       |
| flat_or_apartment  | varchar(50)  | NO   |     | NULL    |       |
| street             | varchar(50)  | NO   |     | NULL    |       |
| town               | varchar(150) | NO   |     | NULL    |       |
| address            | varchar(150) | NO   |     | NULL    |       |
| city               | varchar(150) | NO   |     | NULL    |       |
| district           | varchar(152) | NO   |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

The data:

"000000D6-CFA4-476E-95A4-8680BE96B482","181995","2005-12-14 00:00:00","ST11 9TL","S","Y","F","12",,"HOFFMAN DRIVE","BLYTHE BRIDGE","STOKE-ON-TRENT","STAFFORD","STAFFORDSHIRE","A","A"
"000000FE-94CA-47DA-8D75-6FDFA5960D75","75000","2002-03-20 00:00:00","DA9 9PT","F","N","L","20",,"SWALLOW CLOSE","GREENHITHE","GREENHITHE","DARTFORD","KENT","A","A"
"0000012D-3A97-4FF7-BCA7-897FAA91E25B","52500","1997-06-27 00:00:00","BS20 6JQ","S","N","F","14",,"AVON WAY","PORTISHEAD","BRISTOL","NORTH SOMERSET","NORTH SOMERSET","A","A"

I hope I have not confused anyone. I have tried to keep the post as basic but at same time providing an understanding of the issue I face and what setup I have.

Any help will be a god send, I have tried for weeks to resolve this!

Answer 1

All you're missing is an index on postcode. In your query you're doing this: where('postcode', '=', $pcode

It almost certainly translates at the database layer to select a, b, c from table where postcode = x.

Without the index on postcode you're doing a slow scan of the entire table, with an index you can do a fast seek.

Answer 2

Most likely you have PRIMARY KEY defined as PRIMARY KEY (uuid,sale_date,postcode), and your execution plan looks similar to

MariaDB [test]> explain select uuid, postcode, address, sale_date from postcode_records where postcode = 'BS20 6JQ';
+------+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table            | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | postcode_records | ALL  | NULL          | NULL | NULL    | NULL | xxxxx | Using where |
+------+-------------+------------------+------+---------------+------+---------+------+-------+-------------+

where xxxxx is the number of rows in the table.

If it's so, try to drop the primary key and instead add it as

ALTER TABLE postcode_records DROP PRIMARY KEY;
ALTER TABLE postcode_records ADD PRIMARY KEY (postcode,uuid,sale_date);
ANALYZE TABLE postcode_records;

And run EXPLAIN again. It should become more like

+------+-------------+------------------+------+---------------+---------+---------+-------+-------+-------------+
| id   | select_type | table            | type | possible_keys | key     | key_len | ref   | rows  | Extra       |
+------+-------------+------------------+------+---------------+---------+---------+-------+-------+-------------+
|    1 | SIMPLE      | postcode_records | ref  | PRIMARY       | PRIMARY | 17      | const | zzzzz | Using where |
+------+-------------+------------------+------+---------------+---------+---------+-------+-------+-------------+

The query itself should become faster. Whether it's sufficiently faster or not, remains to be seen in your environment.

Additionally, if the table is InnoDB, check the value of innodb_buffer_pool_size and increase it if it's not big enough.