mORMot Open Source

tbo

Member

Registered: 2015-04-20

Posts: 353

Does the arrangement of the fields influence the speed of the database

Hallo!

A long time ago, when the parents of many forum members were children themselves, it was said that the arrangement of the fields (field type) influences the speed of the database. Fixed length fields were inserted before string fields.
Therefore I have arranged fields like this: xID: TID, yID: TID, nameX: RawUTF8, nameY: RawUTF8...
Is this still the case today? And if so, which databases are particularly affected?

With best regards,
Thomas

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,673

Website

Re: Does the arrangement of the fields influence the speed of the database

It is highly dependable on the DB itself.

On SQLite3, it doesn't make any difference.
On PostgreSQL, plain TEXT field with no length is faster than alternatives (especially faster than fixed-size fields).
On some DB, using non fixed-size fields may need to use the CLOB API, which may be slightly slower.

Grouping the fields by type doesn't change anything.

Our ORM uses "fast enough" (tm) DB types when it creates the table schema.

Sidenote: fixed-size fields is clearly a reminiscence from the 1980s - with modern hardware, fixed-size fields on disk is just a waste of space, and therefore performance.

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: Does the arrangement of the fields influence the speed of the database

Hallo!

Thanks a lot Arnaud for the answer. Yeah, that was in the 1980s. That's when I started learning programming for private purposes.

With best regards
Thomas

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Does the arrangement of the fields influence the speed of the database

For Postgres the good explanation is in Postgres WiKi Dont do this
For Oracle CLOB is a real performance problem (try to avoid it)
But as @ab say field order does not metter for all modern database