Explaining SQL Explain

MySQL and MariaDB provide a useful EXPLAIN command which analyzes queries and detect potential performance issues. EXPLAIN describes how a SELECT will be processed including information about JOINS. EXPLAIN EXTENDED provides additional information and estimates the number of table rows that are filtered by the condition. Everyone uses this frequently. Now- how to interpret a query’s explain plan-
Continue reading

SQL: hierarchical recursive query

This post is merely a self reminder. Using hierarchical data in a SQL database is very common. It’s complexity clearly points that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical, but are simply a flat list. But practical usage of parent-child relationship data structure is quite useful.
Continue reading