0

I did quite good research and went through some information on this regard. But I am not satisfied yet, so preferred to ask directly about it.

I'm designing the schema of an application database, which is assumed to have tins of millions of records.

Some of these are frequently updated, and some are very rarely updated. Hence, I decided to spread data on different tables according to the nature, and JOIN them in queries.

So, is this better or.. going with the one table (no JOINs)?

Assume it is a Rent-A-Car management system..

Here are the tables:


DESC cars
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | ai    |
| brand           | varchar(40)         | NO   |     | NULL    |       |
| engine_capacity | tinyint(4)          | NO   |     | NULL    |       |
| model           | tinyint(4)          | NO   |     | NULL    |       |
| serialnumber    | bigint(20)          | NO   | UNI | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+

AND

DESC cars_data;
+---------------+----------------------+------+-----+---------+-------+
| Field         | Type                 | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| carid         | bigint(20) unsigned  | NO   | PRI | NULL    | ai    |
| car_status    | enum('1','2','3')    | NO   | MUL | 1       |       |
| base_location | point                | NO   | MUL | NULL    |       |
| driver_id     | bigint(20) unsigned  | NO   | MUL | NULL    |       |
+---------------+----------------------+------+-----+---------+-------+

Both those tables, are assumed to be very rarely updated, thus I used for them MyISAM for next reasons:

  • Need to store Point data and use Spatial Index on them.
  • Saving disk space (since MyISAM tables are smaller in size).
  • Easier to maintain (as per my experience, is that right?).

Here is the very frequently updated table:

DESC cars_extra;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| carid         | bigint(20) unsigned | NO   | PRI | NULL    | ai    |
| odometer_km   | tinyint(6) unsigned | NO   |     | NULL    |       |
| last_maint    | bigint(20) unsigned | NO   |     | NULL    |       |
| last_update   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

I used for last table InnoDB for one single main reason:

  • Avoid table-level locks during updates.

Advises, and general notes would be more than appreciated :-)

Also, are joins usually a con or pros (surly with the use of proper indexed keys).

  • 1
    just as advice - look for 5.7 new features - percona.com/blog/2016/02/03/new-gis-features-in-mysql-5-7 MyISAM and InnoDB not only different in part of table locks, but have as well different resource usage, and server properly configured for InnoDB will be not so effective for MyISAM (and opposite) – a_vlad Nov 23 '16 at 1:16
  • @a_vlad Thanks for the great tip! I just went ahead and upgraded the cluster to 5.7, Awesome! – Hasan Alsawadi Nov 23 '16 at 8:48
  • Need to test of course - for example FullText on InnoDB work little different, I not sad - bad, but some queries return different result in FullText ... so there also need test, I not use them, so can not share expeerience – a_vlad Nov 23 '16 at 9:33
  • @a_vlad On this particular project, I don't really need the full text indexing at all. And in regard to the Spatial index, seems to be working just perfect on InnoDB, with slight better performance, on exact same old queries. – Hasan Alsawadi Nov 23 '16 at 11:20
2

Simply, use InnoDB for everything. MyISAM is going away in the next release. In almost all cases, InnoDB out-performs MyISAM. About the only advantage with MyISAM is disk space.

With millions of rows, you will appreciate InnoDB's automatic recovery after any crash.

It is rarely wise to make two tables be 1:1. Your suggestion for that is based on one table being constant; the other being frequently modified. But I would suggest that "frequent" is 'many times a second', not 'a few times a day'.

Don't design the schema without sketching out the SELECTs. You have POINT and SPATIAL, but what will you do with such?

Glancing at the tables...

Use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

Don't use BIGINT (8 bytes) unless INT (4 bytes) won't suffice. You would need to hire nearly all the adults in the world to exceed what INT UNSIGNED can handle.

JOIN can be pro or con. But it is rarely a deciding factor.

Use utf8mb4 from the start. (DESCRIBE omits that bit if info.)

Use AUTO_INCREMENT only if there is not a 'reasonable' column (or combination of columns) that make up a 'natural' PRIMARY KEY.

TINYINT UNSIGNED is limited to 255, not enough for odometer. MEDIUMINT UNSIGNED goes up to 16M.

Do not PARTITION without a good reason for it.

  • Very precious amount of information! Thanks.. :-) – Hasan Alsawadi Nov 23 '16 at 5:38
  • In regard to utf8mb4, should I go for utf8mb4_bin or utf8mb4_general_ci ? – Hasan Alsawadi Nov 23 '16 at 6:03
  • utf8mb4_unicode_520_ci, if available in the version you are using, is the latest standard. – Rick James Nov 23 '16 at 6:05
  • I for 200% agree for InnoDB, but ... but because this indexes new - need test and compare. I have one project which still on MyISAM (with many millions records) .. because FullText on InnoDB return different result for some queries, it not mean wrong, but realisation of FT - little different, so there also - need test and analyse results – a_vlad Nov 23 '16 at 9:36
  • I also noticed, that table size itself dropped when switched to InnoDB, something I wondered about, with ~12 millions records on table. Maybe the way InnoDB stores Point and Spartial indexes out-comes MyISAM and so the data size got saved? Not sure.. – Hasan Alsawadi Nov 23 '16 at 11:22

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.