Die Stumme Ursel

Ah, my next "favourite" topic - the almighty ORM (just kidding). From the OOP principles point-of-view any ORM (or similar) tool is an anti-pattern. I wouldn't start that flame now. 
In my humble opinion using ORMs is making developers bad at database design. The most common mistakes are:
Improper relations between entities Lack of or dump indices Using ORM classes as anemic objects with data I am not saying that ORM tools are bad at all, but let me ask you following questions:
How many developers do you know to be using proper ERD (Entity Relationship Design) tools? How many of them are using Generalisation / Specialisation properly when it comes to ER modelling? How many of them knows how to use database indices properly? How many of them understands Database Normalisation in general and Normal Forms? Having to know when each tuple was created (created_at) or modified (modified_at) doesn't add that much value to neither your product nor data. The other funny part is so called "soft delete". Having such "hidden" rows in your table(s) would lead to database fragmentation and overall performance penalties. Let's see it in action, assuming we have following structure 
CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(64) NOT NULL, `email` varchar(255) NOT NULL, `email_verified_at` timestamp NULL DEFAULT NULL, `password` char(40) DEFAULT NULL, `remember_token` varchar(255) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `email_unique` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 And have following considerations:
We are using (almost) standard users table provided by Laravel framework We are using TIMESTAMP columns for created_at and updated_at to utilise CURRENT_TIMESTAMP predicate and no to bother with handling these columns in our code (That's the power of Eloquent ORM here) We are trying to put our data footprint as small as possible hence password column is CHAR(40) assuming we will have SHA1 hashes instead of plain text password Now let's try to find most recently created user, a simple SELECT query would looks like
SELECT * FROM users ORDER BY created_at DESC LIMIT 1; But what happen behind the scene is
MariaDB [d1]> EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 1; +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) Definitely RDBMS is doing a full table scan. It will be blazingly fast with 5 rows of data but what about 500K rows?
Having "Using filesort" would lead to temporary table to be created in order to perform sorting and then limitation of the result set. When working with smaller result sets such temp. tables will be created in memory - which will be fast enough. Increasing numbers of rows in the table, ie. amount of data stored in the table, would lead to temp. tables to be created on disk - which is slow and resource consuming process. Also, such temporary objects have to be freed properly adding more time to overall execution time.
Now, given the fact we are aware of our database structure and we know how indices and auto_increment works we could make following assumptions:
All user IDs are generated automatically in (almost) sequential order All timestamps are generated automatically at same time as user ID Hence user with ID of 5 will be (or should be) created after user with ID of 4 Using different approach to fetch the most recently created user will give us
MariaDB [d1]> EXPLAIN SELECT * FROM users ORDER BY id DESC LIMIT 1; +------+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | users | index | NULL | PRIMARY | 4 | NULL | 1 | | +------+-------------+-------+-------+---------------+---------+---------+------+------+-------+ 1 row in set (0.00 sec) But what if we create an index on created_at column to speed our first query, erm let see
MariaDB [d1]> ALTER TABLE users ADD INDEX created_at (created_at); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [d1]> EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 1; +------+-------------+-------+-------+---------------+------------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------------+---------+------+------+-------+ | 1 | SIMPLE | users | index | NULL | created_at | 4 | NULL | 1 | | +------+-------------+-------+-------+---------------+------------+---------+------+------+-------+ 1 row in set (0.00 sec) It's the same result as using our primary key. So far so good one would say. If we end adding indices to all columns in our table then we will have index trees with the same size as underlying data which will render our indices unusable. Let see what we have when it comes to data storage
[mind@hive ~]# du -h /var/lib/mysql/d1/users.* 4.0K /var/lib/mysql/d1/users.frm 11M /var/lib/mysql/d1/users.ibd I am using innodb_file_per_table = 1 to optimise InnoDB storage but all in all - our table uses 11MB for storing our data (the actual size is less but that's not the point). What will be the size if we get rid of created_at index then?
MariaDB [d1]> ALTER TABLE users DROP INDEX created_at; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [d1]> ALTER TABLE users ENGINE=InnoDB; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 [mind@hive ~]# du -h /var/lib/mysql/d1/users.* 4.0K /var/lib/mysql/d1/users.frm 2.1M /var/lib/mysql/d1/users.ibd We could say the overhead of data storage was 550% having 10K rows in our table. Proving that adding indices with high cardinality is not a good idea storage- and performance-wise.
My previous examples where given to prove why date / date time / time related columns are broken by design. My current examples explains why using such columns to filter data are slower. Long story short - more data to seek in (ie. bigger storage) leads to more teach to do the search. Using DATETIME instead of TIMESTAMP is even worse because it uses 8 bytes (without fractional seconds) compared to 4 bytes.
Alas, when it comes to different environments figures will be very different. Doing tests on local (isolated) environment with handful of objects (databases, tables, rows in the tables, etc) is a way different that running same queries on heavy-loaded production servers using replication for example.
To give you a more natural example - let say we have our users table presented as a notebook and having each row written on it's own page. Which one will be fast:
Reading all pages to compare all created_at value, or Going to last page to read it's created_at value This applies on scenario with index on created_at column too because it is a secondary index.
 
Every bit of data sent by user should be seen as untrustworthy indeed. I couldn't agree more.
Disabling the UI is called Display Morphing, Representation Morphing pattern or more commonly known as in-place edit but yet could be best possible solution.
 
-Ursel

i love listening music while working but had to stop when doing some intense coding that require my full attention.  Love classics like Metalica, U2, Bon Jovi etc