0

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!

3

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.

  • Would this still be the case if postcode, sale_date and uuid are primary keys as I thought MySQL made them indexes automatically though I maybe wrong as I am still learning :) thanks – Birdy Dec 3 '16 at 18:04
  • The order of columns in the primary key matters. It's unlikely postcode is the first part of the primary key. But the proof is in the slow performance. – Cody Konior Dec 3 '16 at 18:09
  • Just put a separate index on postcode and let the uuid be the PK – paparazzo Dec 3 '16 at 19:01
  • 1
    @CodyKonior - Excellent it worked instantly, I was close to trying this last week and assumed it was something much more complicated, my inital thought was my configuration! When you sit stearing at code for days on end you overlook the simple solutions and assume the worst case! Thanks for taking time out to advise me, The response is now 20ms so very happy with the result. Have a good christmas and thanks for your time! – Birdy Dec 3 '16 at 20:42
0

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.

  • This would allow for duplicate uuid – paparazzo Dec 3 '16 at 18:49
  • They are already allowed by the existing composite key, modifying the order won't change anything there. And it's not important, since the table does not get updated. – elenst Dec 3 '16 at 18:53
  • Number 2 in the example in the question would not have worked if there were duplicates. The name would make no sense if duplicates are allowed. – paparazzo Dec 3 '16 at 18:56
  • There are NO duplicates in the data (the text says it, no need to deduct), but they are ALLOWED by the existing composite primary key. Changing the order of fields in the primary key does not anyhow affect either uniqueness of the key in general, or non-uniqueness of any individual field. And it is not important, because the table is never updated. The table already has unique UUIDs, which was apparently insured by other methods, and they will remain unique. – elenst Dec 3 '16 at 19:01
  • Are none and not allow duplicates are not the same. Never say never. There is no purpose to not enforcing uniqueness. – paparazzo Dec 3 '16 at 19:03

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.