What every developer needs to know about database optimization
Relational databases are the workhorse of data storage. While every year there is anew hyped storage system for some specialised use case, relational databases continue to assimilate the best parts of the hype and gain capabilities. And due to the strong standardization the SQL we use to talk to databases is pretty universal and we can relatively easily move from one database to another. But the way relational databases work on the inside is also surprisingly similar. Or not so surprisingly: there have been thousands of research papers written about them (some older than any existing database) and they all try to solve the same problem, so it does make sense they copied the best ideas from eachother.
This talk will show how a SQL statement is processed by a database, which steps the database takes, the main data structures in the database, how to optimize queries and how CFML tags affect these database internals. Code examples are from MS SQL Server and PostgreSQL, but the mechanisms apply equally to MySQL/MariaDB, Oracle, DB/2, SQLite etc.
Jochem van Dieten