サクサク読めて、アプリ限定の機能も多数！
トップへ戻る
ノーベル賞
explainextended.com
Translations: Russian This year, the talk of the town was AI and how it can do everything for you. I like it when someone or something does everything for me. To this end, I decided to ask ChatGPT to write my New Year's post: "Hey ChatGPT. Can you implement a large language model in SQL?" "No, SQL is not suitable for implementing large language models. SQL is a language for managing and querying d
From Stack Overflow: When I run an SQL command like the one below, it takes more than 15 seconds: SELECT * FROM news WHERE cat_id = 4 ORDER BY id DESC LIMIT 150000, 10 EXPLAIN shows that its using where and the index on (cat_id, id) LIMIT 20, 10 on the same query only takes several milliseconds. This task can be reformulated like this: take the last 150,010 rows in id order and return the first 10
In one of my previous New Year's posts we drew snowflakes in PostgreSQL. The algorithm we used to create the snowflakes is an implementation of an L-system, which is an example of a fractal. There are many more beautiful objects we can see in the winter: frozen trees, frost patterns on windows, cracks on ice etc., all of them being fractals. Today we will be constructing escape-time fractals. To b
We have a nice tree sorted as a tree, with rows indented according to the depth level. In the query above, START WITH defines the root of the tree, and CONNECT BY defines join condition between parent and child rows. Parent columns are defined by adding PRIOR keyword to them. In MySQL there is no such construct, but it can be emulated. We may try construct a sorting function somehow, but it will b
Which method is best to select values present in one table but missing in another one? This: SELECT l.* FROM t_left l LEFT JOIN t_right r ON r.value = l.value WHERE r.value IS NULL , this: SELECT l.* FROM t_left l WHERE l.value NOT IN ( SELECT value FROM t_right r ) or this: SELECT l.* FROM t_left l WHERE NOT EXISTS ( SELECT NULL FROM t_right r WHERE r.value = l.value ) Let's see for PostgreSQL 8.
(I just discovered cracked.com) #10. Searching for a NULL SELECT * FROM a WHERE a.column = NULL In SQL, a NULL is never equal to anything, even another NULL. This query won't return anything and in fact will be thrown out by the optimizer when building the plan. When searching for NULL values, use this instead: SELECT * FROM a WHERE a.column IS NULL #9. LEFT JOIN with additional conditions SELECT
More than 13 seconds, and it's only a million rows. And financial databases may have billions. But do we really need sorting anyway? Say, we need to select 10 rows out of 1 000 000. If we just iterate the rows, which is the probability of each row to be selected on its turn? As no rows differ from each other for our purposes, each remaining row may be selected with equal probabilty. This means, th
Which method is best to select values present in one table but missing in another one? This: SELECT l.* FROM t_left l LEFT JOIN t_right r ON r.value = l.value WHERE r.value IS NULL , this: SELECT l.* FROM t_left l WHERE l.value NOT IN ( SELECT value FROM t_right r ) or this: SELECT l.* FROM t_left l WHERE NOT EXISTS ( SELECT NULL FROM t_right r WHERE r.value = l.value ) Finally, it's MySQL time. A
Regular readers of my blog will be aware that SQL is an excellent tool for graphics. You can use it to draw snowflakes, fractals, ray-traced 3D pictures, and many other things. SQL art is beautiful, albeit slow to generate. These days they say AI is taking over, and human-made art will soon go the way of the dodo. The same fate awaits SQL-made art, I'm afraid. But you can't stop the progress. If y
このページを最初にブックマークしてみませんか？
『EXPLAIN EXTENDED』の新着エントリーを見る
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く
