Few useful MySQL queries

I had kept this post private for few years. Recently realised that I visit this article quite frequently and update more often. It combines different SQL query types e.g. Update multiple rows with different values, Identify/ Fix rows which consist invalid dates, Explain query warnings or Check DB size etc. This article follows MySQL query patterns; should be almost compatible with other RDBMS too. If you get your hands dirty with SQL queries regularly, check this out, I think you will find out few queries useful for you too.
BTW, as they say itdilbert
Continue reading

Advertisements

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