Tobias_Petry.sql
@tobias_petry
Followers
21,245
Following
278
Media
553
Statuses
7,827
The Database Guy. I am helping you get better with MySQL and PostgreSQL. Building: @stackbricksapp
Darmstadt, Germany
Joined June 2013
Don't wanna be here?
Send us removal request.
Explore trending content on Musk Viewer
メイドの日
• 309228 Tweets
メイドさん
• 119795 Tweets
メイド服
• 91509 Tweets
#それスノ
• 82033 Tweets
#Mステ
• 70613 Tweets
सुप्रीम कोर्ट
• 63178 Tweets
太陽フレア
• 56023 Tweets
ラウール
• 33196 Tweets
aiko
• 32531 Tweets
ONGSASUN CONFLICT
• 32211 Tweets
#BACKSLIVEに油を注せ
• 22837 Tweets
モバイルSuica
• 22734 Tweets
チャージ
• 22448 Tweets
#阪神タイガース
• 22424 Tweets
DDoS
• 20819 Tweets
岸辺露伴
• 20445 Tweets
ジェシー
• 16146 Tweets
サイバー攻撃
• 16123 Tweets
世界大会
• 14784 Tweets
アニサマ
• 12373 Tweets
小林ホームラン
• 11321 Tweets
ノイジー
• 10887 Tweets
ミスチル
• 10809 Tweets
階段降り
• 10282 Tweets
Last Seen Profiles
Pinned Tweet
I am thrilled to announce the official launch of the
@IndexingBook
.
Do you lack indexing knowledge? It has never been easier to learn! This broad topic is explained in a novel, easy-to-understand way supported by graphics that visualize all concepts. 🔥
32
114
424
PHP did the markup and server code mixing 20 years ago and collectively decided its a bad idea as it becomes unmaintanable pretty fast.
Now that gets further complicated by also mixing with frontend-stack complexity...
Can somebody explain to me why that is a good idea now?
490
566
4K
⚡️ Database Tip
For e.g. a dashboard, multiple queries on the same rows have to be executed to calculate different statistics. However, these queries can also be combined into one query that has to scan the rows only once!
22
201
1K
Your app's biggest performance problems are probably databases, not tech-stack related.
Your app's biggest performance problems are probably backend, not frontend related.
91
177
2K
38
63
883
⚡️ Database Tip
Sometimes you have date ranges in your database and you don't want them to overlap. Two people staying in the same room at the same time in a hotel is a big problem.
You can define constraints for your tables so that time ranges can never overlap!
16
136
841
⚡️ Database Tip
Sometimes you want to get e.g. the last three orders for *every* customer. Normal joins can't do that. You have to execute n+1 queries in code, which is slow!
But with lateral joins, you can do one for-each loop join in SQL 🤯
35
160
787
35GB is a joke for any database. There is no need to change anything.
Always find out if something is really slow or you just think it is. And when you found slow queries, just optimize them.
Suggesting to switch the database technology is the worst feedback you can give.
What's the SQLite strategy for when databases get too big?
Photo AI's photos.db is 35.2GB with 7,270,478 photos in it now (just metadata, pics are stored on R2)
Should I make a script that empties older photos and put them in a photos_archive.db and keep the photos.db fast?
254
35
1K
15
59
750
🎉 The FREE MySQL Explain Tool is live
Even after years of optimizing queries, I find MySQL's EXPLAIN output hard to understand. For someone with less experience, it is almost impossible.
So I've built a free tool in the past months to simplify it.
18
168
649
⚡️ Database Tip
For e.g. a dashboard, multiple queries on the same rows have to be executed to calculate different statistics. However, these queries can also be combined into one query that has to scan the rows only once!
8
99
637
⚡️ Database Tip
Did you know there are alternative ways of storing trees in a database without a parent_id and many queries to get all ancestors/children or the complex nested set concept?
You can store the materialized path for every row to get much easier tree management!
31
106
607
⚡️ Database Tip
Sometimes you have very complex queries involving multiple calculation steps. Instead of nesting queries or doing it in application code you can use Common Table Expressions (CTE) to refine your data in every step and even reuse former steps.
17
119
600
⚡️ Database Tip
Creating indexes is a handcraft learned from experience, multiple-column indexes is a lot more complicated. But it doesn't have to be complicated!
Here are 3 simple rules for the column ordering of multiple column indexes, solving 80% of the perf problems.
23
132
599
⚡️ Database Tip
Sometimes you have very complex queries involving multiple calculation steps. Instead of nesting queries or doing it in application code, you can use Common Table Expressions (CTE) to refine your data in every step and even reuse former steps.
19
86
583
⚡️ Database Tip
Did you know there are alternative ways of storing trees in a database without a parent_id and many queries to get all ancestors/children or the complex nested set concept?
You can store the materialized path for every row to get much easier tree management!
24
93
587
⚡️ Database Tip
Everyone knows the LIMIT clause. But what do you do if you want to have e.g. the employees with the TOP 3 salaries *and* not exclude the 4th or 5th one earning the same as the 3rd? The WITH TIES clause can return more results if the value is the same. Great!
13
103
557
So Vercel pushed the idea of edge rendering to an entire generation of developers. And now admits that it was a bad idea...
And why? Because your database is not at the edge. Its somewhere else and your paying big latency for _every_ query.
Told you all the time!
So,
@vercel
reverted all edge rendering back to Node.js 😬
Wanted to correct the record here as it's something I've advocated for in the past, and share what I've learned since then.
Also, the "edge" naming has been a bit confusing, so let's clear that up here as well.
What is…
113
206
2K
30
47
556
⚡️ Database Tip
Deleting an unused index is still a risky operation: If you are wrong (and it is still needed) the database will become slow. Before deleting it, you can make the index invisible first and make it visible again instantly if you still need it.
14
82
547
[Howto] Creating Indexes for JOINs
- an excerpt from the upcoming
@IndexingBook
Optimizing joins seems complicated. How should the following query be optimized? It is using multiple tables and not just one!
👇 The full explanation
3
71
532
I've been sharing weekly database tips on Twitter for one year. To celebrate this milestone, I've repackaged the best ones into an eBook 🎁
Get your *FREE* copy and discover so much you didn't know before! And follow me for more ;)
19
130
538
⚡️ Database Tip
Sometimes you want to make columns unique but you can't because e.g. there are still soft-deleted rows in the table witht the same value. By only including relevant rows (partial index) you can still enforce uniqueness, and you can even simulate it for MySQL!
14
98
526
⚡️ Database Tip
Sometimes you want to get e.g. the last three orders for *every* customer. Regular joins can't do that. You have to execute n+1 queries in code, which is slow!
But with lateral joins, you can do a for-each loop join in SQL
8
65
516
SQL is the only thing you can learn today and is still not outdated in 10+ years. The most rewarding thing: learn once, apply your whole career.
25
69
513
So basecamp is using their MySQL database as a queue running millions of jobs each day.
But you say that a database couldn't handle your (tiny) load and you have to use something more complex?
I doubt it.
We've shipping Mission Control: The missing web UI for Solid Queue! This is what we use internally to run millions of daily jobs. Please help us refine it for inclusion in Rails 8.
4
40
388
24
50
503
A database index cannot be infinitely large.
At some point, you will get an error message that the size limit is exceeded and the index creation is canceled.
But there are some tricks that you can still create an index for e.g. many big string columns.
👇 Learn More
5
63
496
⚡️ Database Tip
Sometimes you want to get e.g. the last three orders for *every* customer. Normal joins can't do that. You have to execute n+1 queries in code, which is slow!
But with lateral joins, you can do one for-each loop join in SQL 🤯
11
84
476
A Different View On B+ Trees For Database Indexes
- a chapter preview from the upcoming
@IndexingBook
that I am writing
Each article or book explaining database indexes starts by describing what B+ trees are: the distinction of leaf nodes (the most bottom rectangles) vs. inner…
5
56
463
⚡️ Database Tip
Sometimes you have date ranges in your database and you don't want them to overlap. Two people staying in the same room at the same time in a hotel is a big problem.
You can define constraints for your tables that time ranges can never overlap!
13
101
461
📣 Announcement: I quit my job!
For a long time, I felt the need to change something. So from today on, I am a freelance database consultant!
And as I now have much more time to work on stuff, you will see many cool things in the following months. Stay tuned!
49
8
452
Here's the first screenshot of {Project Foo} which I am working on. A free desktop application to run multiple different databases versions (even ancient ones) all at the same time on your development machine. It will have full support for linux, macOS (intel + m1) and windows.
27
46
443
⚡️ Database Tip
When doing statistical aggregations by time, you may have e.g. days without data leading to strange results when charting the results. But you don't have to fill these gaps manually with code, the database can do that for you.
15
65
442
⚡️ Database Tip
Sometimes you want to get e.g. the last three orders for *every* customer. Regular joins can't do that. You have to execute n+1 queries in code, which is slow!
But with lateral joins, you can do a for-each loop join in SQL
7
77
440
⚡️ Database Tip
Most developers are puzzled that indexes are not used for e.g. WHERE LOWER(email) = ?. But contrary to common belief, an index can be created for these cases! It's called a function-based index and is supported by MySQL and PostgreSQL.
8
76
426
⚡️ Database Tip
You don't have to execute multiple queries to calculate different aggregates. With the filter clause you can narrow the rows which should be included for the calculation. So you may need to scan the table only once, the performance impact can be massive 🔥
10
74
428
⚡️ Database Tip
Everyone knows the LIMIT clause. But what do you do if you want to have e.g. the employees with the TOP 3 salaries *and* not exclude the 4th or 5th one earning the same as the 3rd? The WITH TIES clause can return more results if the value is the same. Great!
13
70
424
A common problem in databases is hot rows that need to be updated very often, e.g. counters.
I've used this pattern for a long to remove write contention lock but didn't know it has a name.
12
78
422
[Howto] Creating Indexes for Subqueries
- an excerpt from the upcoming
@IndexingBook
Subqueries are still believed to be slow, although they are just missing matching indexes. Creating good ones for subqueries is not particularly complicated.
👇 The full explanation
3
59
415
⚡️ Database Tip
Deleting hundreds of thousands of rows is slow, impacts your performance and doesn't free the allocated disk space. But you can delete old data very efficiently if you partition your table by time ranges and drop one of them.
6
49
410
⚡ Database Tip
Creating indexes is a handcraft learned from experience, multiple-column indexes is a lot more complicated. But it doesn't have to be complicated!
Here are 3 simple rules for the column ordering of multiple column indexes, solving 80% of the perf problems.
14
74
399
⚡️ Database Tip
Every tutorial for pagination is sharing the same inefficient and wrong concept of pagination with OFFSET and LIMIT. Cursor pagination is much more efficient and won't show rows multiple times which will happen sometimes! It's a simple change, just do it!
21
59
393
⚡️ Database Tip
When storing JSON values in your database, you should still use a schema. MySQL does provide the excellent function JSON_SCHEMA_VALID which will validate the column against a
@jsonschema
definition. Make use of it!
8
72
389
Your SQL query is slow and you need to create an index. But which one?
An index on a single column is wrong 99% of the time...
👇 Learn with 5 tweets why you always need a multi-column index
19
47
440
Beware of MySQL adding columns after another one.
Before 8.0.29 the full table needs to be re-created when this option is used which locks it up until finished. Without the option, the column can be added instantly.
So is the ordering that important to you? 😉
This
@laravelphp
tip never gets old! If you wanna throw in a bunch of columns right after an existing column, just go ahead and rock that "after()" method. 💅🏻
24
48
340
33
55
359
Why Can a Unique Index Store the Same Value Twice?
If your unique index includes nullable columns, its behavior to enforce uniqueness may not be what you expect it to do.
👇 Learn More
8
47
355
After much work, the new landing page for the upcoming
@IndexingBook
is live 🥳
It showcases everything that makes this book different from anything you've seen before.
What's your opinion? Good? Bad? Any ideas for improvement?
21
54
347
And thats an honest question! I am so far away from the frontend world that I just can't understand the reasoning.
Did they decide now that creating all those API endpoints is too much work?
21
4
331
⚡ Database Tip
Recursive queries are a great solution to e.g. querying multiple levels in a tree with just one query. But you should use cycle detection otherwise your query might run forever or fail for loops in your data.
7
58
330
⚡️ Database Tip
Deleting hundreds of thousands of rows is slow, impacts your performance and doesn't free the allocated disk space. But you can delete old data very efficiently if you partition your table by time ranges and drop one of them.
7
51
333
Have you tried optimizing a query with EXPLAIN? I often do not understand it myself. So much (useless) information and the important ones are hard to see.
That will no longer be the case! I wrote a *FREE* tool to make this output easy to understand.
More infos soon... 😉
17
29
332
⚡️ Database Tip
Most developers are puzzled that indexes are not used for e.g. WHERE LOWER(email) = ?. But contrary to common belief, an index can be created for these cases! It's called a function-based index and is supported by MySQL and PostgreSQL.
9
56
326
⚡️ Database Tip
Most developers are puzzled that indexes are not used for e.g. WHERE LOWER(email) = ?
Most developers are unaware of it, but you CAN create an index so this will be very fast!!! It's called a functional index and is supported by MySQL and PostgreSQL.
10
68
328
How To Create Multi-Column Indexes: From Left to Right
- a chapter preview from the upcoming
@IndexingBook
that I am writing
An index on a single column is simple, easy to understand and nobody has problems using them. The real problem and performance improvement opportunity is…
8
49
316
⚡️ Database Tip
There are alternative ways of storing trees in a database without a parent_id relationship or the complex nested set concept. You can store the materialized path for every row for easier tree management!
13
40
321
⚡ Database Tip
Duplicate rows are created easily by mistake, but can be deleted much easier:
You can in a *single* SQL query specify the duplicate rows to keep and also keep more than one!
14
65
317
👀 New Preview for my MySQL Explain Tool
Even EXPLAINs for simple joins display a lot of meaningless internal information about a query's execution. While the important ones are hidden.
Compare what MySQL shows and what I extract from that. What do you like more? 😉
21
30
303
⚡️ Database Tip
There can be a faster way to group a table by multiple attributes compared to executing many queries.
With GROUPING SETS the table needs only to be scanned once instead of multiple times. The performance improvement can be massive 🔥
6
68
297
👀 New Preview for my MySQL Explain Tool
Simple plans are easy to understand. But with subqueries? It is now much easier to understand the relationship between the main query and subqueries - and whether they are independent or dependent.
Release next week 😉
20
28
292
I say the same for years: Choose a relational DB (PostgreSQL/MySQL), add Redis for caching etc and optionally add an OLAP DB like ClickHouse for large-scale analytics.
DON‘T COMPLICATE YOUR TECH STACK WITH FAANG SOLUTIONS DESIGNED FOR MUCH BIGGER SCALE!
11
46
281
You may not need a gazillion servers for your startup. Stack Exchange is running on 23 servers serving 1.3B requests/day. They use a few beefy machines instead of a lot more commodity servers to keep their maintenance low.
Database Servers? Only 4! 🤯
8
62
284
🎉 My change for Laravel 10.x was merged to replace DB::raw() with database expressions that can generate SQL code dependent on the database being used!
It would be great if everyone can respond with actual raw() statements used, that I should support! 👇
18
23
278
⚡️ Database Tip
Sometimes you are updating the same rows concurrently, e.g. with counters. But each query has to wait for all the ones before to finish. You can prevent this locking problem by spreading the counter updates to many more rows.
6
52
280
⚡️ Database Tip
Deleting hundreds of thousands of rows is slow, impacts your performance and doesn't free the allocated disk space. But you can delete old data very efficiently if you partition your table by time ranges and drop one of them.
5
53
281
👀 New Laravel Package
Laravel Queries can now be submitted directly from your code to to get understandable EXPLAIN information.
There isn't any more excuse for not optimizing your queries ;)
10
48
277
🎉 Laravel package released
In the past it was troublesome working with raw() statements because they would not work on all databases. But now you can use them with new database-indendepent versions. And use some more advanced stuff...
Check it out:
16
42
266
⚡️ Database Tip
Your application will have bugs and any data stored may be corrupt forever. But with constraints on the data model, rules would have prevented saving incorrect data in the first place! Use them as a last frontier to make sure you always have valid data.
7
49
263
⚡️ Database Tip
When storing JSON values in your database, you should still use a schema. MySQL does provide the excellent function JSON_SCHEMA_VALID which will validate the column against a JSON schema definition. Make use of it!
5
40
260
⚡️ Database Tip
Extending a traditional database schema with NoSQL-like JSON columns can make the schema more understandable. But you should also ensure these JSON documents conform to an expected schema!
12
50
259
⚡️ Database Tip
When doing statistical aggregations by time, you may have gaps in your data. You don't have to fill them manually with code! You can generate a series of dates from a start to end time and use these days to join to your statistical data with gaps.
17
35
253
I still don't have a final name for my ebook that will be released next week.
My first idea was "Database Secrets for Developers" but that is too clickbaity. I don't love the current title "The Database Handbook for Developers" either as I am only sharing many tips.
Your ideas?
68
11
252
⚡ Database Tip
Duplicate rows are created easily by mistake and can be removed almost as easy: You can in a *single* SQL query identify them and also specify which one to keep! It is the fastest way you can do it.
6
42
249
I finished a large chunk on improving the enhanced
@PostgreSQL
driver for
@laravelphp
. The new version has a lot of new indexing functionality to make your database queries really fast and use all the power of PostgreSQL 🔥
👇 All new features explained one-by-one
2
49
251
⚡️ Database Tip
Many applications use the table's numeric primary key in the URL: Bots can scrape all data by incrementing the ID and competitors know your number of customers/resources and growth.Adding a random UUID which is used in URLs solves this.
19
45
249
⚡️ Database Tip
Did you know there are alternative ways of storing trees in a database with simplified querying and much easier tree management? Materialized paths are easier to query than parent_id relationships and easier to update than nested sets.
8
49
247
⚡️ Database Tip
Your application probably has numerous indexes to guarantee fast performance. For columns which are only used in equality checks, you can use hash indexes, which can be smaller and faster. You don't need to use the standard b-tree index always!
13
40
247
⚡️ Database Tip
Tag searching can be done wholly differently: You can store the tag primary keys in a JSON array and effectively search for overlapping tag ids or missing ones without very complex joins. A single where instead of multiple (slow) joins to do the same thing.
12
34
243
With previous patches being merged into Laravel, I was now able to create one that has been requested for quite some years:
User::where(...)->ddRawSql()
It prints the generated SQL query *with* bindings embedded safely into the query 🤯
12
22
245
I use database queues in most projects. They are a lot more performant than you believe. And you can do nice tricks with transaction safeties that job queuing and your logic are perfectly in sync. And one infrastructure component less to manage 😉
14
28
240
⚡️ Database Tip
Sometimes you have date ranges in your database and you don't want them to overlap. Two people staying in the same room at the same time in a hotel is a big problem.
You can constrain your tables to disallow conflicting values!
6
31
239
🎉 I‘ve been awarded a MySQL Rockstar 2022 award for the SQL educating stuff I created last year. 🤩
27
9
237
⚡️ Database Tip
When doing statistical aggregations by time, you may have e.g. days without data leading to strange results when charting the results. But you don't have to fill these gaps manually with code, the database can do that for you.
8
42
234
⚡ Database Tip
Duplicate rows are created easily by mistake, but can be deleted much easier: You can in a *single* SQL query specify the duplicate rows to keep and also specify which one to keep! It is the fastest way you can do it.
8
30
233
⚡️ Database Tip
Many applications are vulnerable to enumeration attacks: The numeric primary key is used in URLs, and incrementing the value will find all records. In my experience, adding a random UUID which is used in URLs instead is the simplest solution for the problem.
16
40
235
phpMyAdmin is still the most widely used MySQL tool but lacks any help in understanding EXPLAIN output.
I proposed adding an option to analyze EXPLAIN plans with my free service to help a big audience of developers tuning queries.
Waiting for feedback 🤞
10
19
229
This is probably the best reason WHY i use PostgreSQL if I don’t need anything very specific:
The rich ecosystem of extensions has solutions for probably everything I may need. I don‘t have to use new-niche-hype-database.
8
28
232
⚡️ Database Tip
With PostgreSQL your INSERT/UPDATE/DELETE queries can return the data of affected rows. You can use this to more efficiently implement many operations by selecting, updating and fetching rows all in one query for maintenance operations.
4
29
228
Spoiler: I am working on a free ebook that compiles my best database tips. It will be launched in a few weeks celebrating 12 months of sharing database knowledge every week 🥳
Can you recommend any ebook covers? I need some inspiration.
13
9
222
Everyone should try
@caddyserver
to replace nginx as it is more easy.
And with FrankemPHP built by
@dunglas
even the PHP integration gets easier and more powerfull - no more php-fcgi needed 🎉
15
33
226
⚡️ Database Tip
Sometimes you want to make columns unique, but you can't because e.g. there are still soft-deleted rows in the table with the same value. By only including relevant rows (partial index), you can still enforce uniqueness.
6
36
222
⚡️ Database Tip
Every tutorial for pagination is sharing the same inefficient and wrong concept of pagination with LIMITs. Cursor pagination is much more efficient and won't show rows multiple times which will happen sometimes!
8
37
223
My free database book is finally on
@ProductHunt
today! 🎉
Did you read it? Did you love/hate it? Did you learn something new? I don't believe you saying no 😛
I would really appreciate it if you share your opinion on PH ❤️
11
38
219
⚡️ Database Tip
Most developers are puzzled that indexes are not used for e.g. WHERE LOWER(email) = ?. But contrary to common belief, an index can be created for these cases! It's called a function-based index and is supported by MySQL and PostgreSQL.
7
35
218
⚡️ Database Tip
In most applications, all rows are physically sorted in the database file by their insertion order. By sorting them instead optimally for your application a huge databases will still be amazingly fast.
8
41
218
⚡️ Database Tip
You don't have to execute multiple UPDATE queries because each one is dependent on some data stored in another table. You can join an UPDATE with other tables to do row updates which depend on data stored in other tables.
3
45
219
⚡️ Database Tip
Most developers are puzzled that indexes are not used for e.g. WHERE LOWER(email) = ?. But contrary to common belief, an index can be created for these cases! It's called a function-based index and is supported by MySQL and PostgreSQL.
10
45
213
⚡️ Database Tip
Sometimes you have date ranges in your database and you don't want them to overlap. Two people booking the same hotel room at the same time is a big problem. You can define constraints for your tables that those ranges never overlap!
7
28
215
Microservices have been invented to scale teams and not applications.
High-traffic serverless is not cheaper than EC2 machines but you can sometimes do more with less effort.
You should know which problem technologies solve and not blindly follow them
13
45
216
⚡️ Database Tip
When doing statistical aggregations by time, you may have e.g. days without data leading to strange results when charting the results. But you don't have to fill these gaps manually with code, the database can do that for you.
8
31
211
⚡️ Database Tip
If you're using numeric primary keys in your URLs, your app may be vulnerable to enumeration attacks. A simple fix? Use random UUIDs in URLs instead while keeping a numeric primary key!
16
34
207
📣 Announcement
I repeatedly see the same misconceptions and need for practical knowledge about indexes. You only find introductory stuff but nothing for real applications. I will change that with a full book dedicated to indexes.
14
46
207