Difference in MySQL JOIN vs LEFT JOIN
I thought that by not specifying a type of join it was assumed to be a LEFT JOIN. Is this not the case? No, the default join is an INNER JOIN. Here is a visual explanation of SQL joins. Inner join Left join
query-optimization
How to do the Recursive SELECT query in MySQL?
Edit Solution mentioned by @leftclickben is also effective. We can also use a stored procedure for the same. CREATE PROCEDURE get_tree(IN id int) BEGIN DECLARE child_id int; DECLARE prev_id int; SET prev_id = id; SET child_id=0; SELECT col3 into child_id FROM table1 WHERE col1=id ; create TEMPORARY table IF NOT EXISTS temp_table as (select * … Read more
Deleting millions of rows in MySQL
DELETE FROM `table` WHERE (whatever criteria) ORDER BY `id` LIMIT 1000 Wash, rinse, repeat until zero rows affected. Maybe in a script that sleeps for a second or three between iterations.
Why does direction of index matter in MongoDB?
MongoDB concatenates the compound key in some way and uses it as the key in a BTree. When finding single items – The order of the nodes in the tree is irrelevant. If you are returning a range of nodes – The elements close to each other will be down the same branches of the … Read more
PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
I would propose a clean version based on DISTINCT ON (see docs): SELECT DISTINCT ON (usr_id) time_stamp, lives_remaining, usr_id, trans_id FROM lives ORDER BY usr_id, time_stamp DESC, trans_id DESC;
JOIN queries vs multiple queries
For inner joins, a single query makes sense, since you only get matching rows. For left joins, multiple queries is much better… look at the following benchmark I did: Single query with 5 Joins query: 8.074508 seconds result size: 2268000 5 queries in a row combined query time: 0.00262 seconds result size: 165 (6 + … Read more
MySQL indexes – what are the best practices?
You should definitely spend some time reading up on indexing, there’s a lot written about it, and it’s important to understand what’s going on. Broadly speaking, an index imposes an ordering on the rows of a table. For simplicity’s sake, imagine a table is just a big CSV file. Whenever a row is inserted, it’s … Read more
SQL: How to properly check if a record exists
It’s better to use either of the following: — Method 1. SELECT 1 FROM table_name WHERE unique_key = value; — Method 2. SELECT COUNT(1) FROM table_name WHERE unique_key = value; The first alternative should give you no result or one result, the second count should be zero or one. How old is the documentation you’re … Read more
Counting DISTINCT over multiple columns
If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns. Once it is persisted, provided the column is deterministic and you are using “sane” database settings, it can be indexed and / or statistics can be created on it. … Read more