CODEWITHSUNDEEP
phpmysqlsqldatabase
Kevin Vermaat
Kevin Vermaat

Reputation: 102

Splitting data into two tables

I want to create a table with this info:

ID bigint(20) PK AI
FID bigint(20) unique
points int(10) index 
birthday date index 
current_city varchar(175) index 
current_country varchar(100) index 
home_city varchar(175) index 
home_country varchar(100) index 
Engine = MyISAM

On school I learned: create 2 extra tables, one with cities and one with countries and FK to that table when inserting data. The reason I doubt is:

This table will have around 10M inserts an hour. I'm afraid if I Insert a row and have to lookup the city FK and country FK every insert, I might lose a lot of speed? And is this worth the gain I get when I am selecting rows which only happens with WHERE ID = id. there will be around 25M of those selects an hour.

Upvotes: 0

Views: 164

Answers (3)

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99921

Premature optimization if the root of all evil. Design cleanly first, and optimize next, when you have actual performance data.

A clean design would be a properly normalized table, i.e. with separate city and a country tables.

I'm afraid if I Insert a row and have to lookup the city FK and country FK every insert, I might lose a lot of speed?

Actually, inserting just small IDs instead of raw country/city names in a varchar column may be more efficient:

  • This will result in less disk writes
  • You have a MyISAM table; so it doesn't have FK support, and doesn't do any foreign key lookup / check
  • Replacing the varchar columns with integers will put the table in fixed-length rows format, which may be faster than the dynamic length format

Benchmark with real data/workload, and see if de-normalizing is really worth it.

Upvotes: 2

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40021

The City and Country tables will be small (relatively) and probably fit nice in memory so lookups will be fast.

If that isn't fast enough try to cache the lookup client side (ie your php-app).

Since your rows will be smaller (int instead of varchar) you can fit more rows on each page making index lookups faster.

Try to do it normalized first, it will probably be fast enough.

And make sure you use InnoDB instead of MyISAM. It has much better locking and your application looks very concurrent.

Upvotes: 1

Twisted1919
Twisted1919

Reputation: 2499

There's a reason why db normalization exists.
Use a table for cities, one for countries and join them with your master table via FK's.
Also, what country do you know having 100 chars in the name?
What city do you know having 175 chars in the name?
ID can be bigint, but are you sure you need a BIGINT(20), wouldn't a INT(11) suffice ? Anyway, AUTOINCREMENT it and don't UNIQUE it, it doesn't make any sense.
Also, you have indexes on every column, but no composite index. This is wrong for so many reasons. Do not pre-index, but index depending on your queries. Use explain to see what's to be indexed.
Also, don't be afraid to use composite indexes and avoid creating indexes for every column that you have.
Do all the above steps and you will have fast queries (let's hope at least)

Upvotes: 1

Related Questions