[ DE ]
byMarkus Winand.
Blog Archive
Translations
中文 对索引Include子句的深入分析
Some database—namely Microsoft SQL Server, IBM Db2, and also PostgreSQL since release 11—offer an include clause in the create index statement. The introduction of this feature to PostgreSQL is the trigger for this long overdue explanation of the include clause.
Before going into the details, let’s start with a short recap on how (non-clustered) B-tree indexes work and what the all-mighty index-only scan is.
(一)Recap: B-tree Indexes
(二)Recap: Index-Only Scan
(三)The Include Clause
(四)Filtering on Include Columns
(五)Unique Indexes with Include Clause
(六)Compatibility
(七)PostgreSQL: No Filtering Before Visibility Check
(Read more)
I haven’t blogged here in a while. It doesn’t mean Use The Index, Luke! is abandoned or anything the like. Au contraire, I’m having some topics in the pipeline I’m hoping to blog soon about. Furthermore, Use The Index, Luke! is regularly updated to reflect changes in newer databases releases.
The last updates were done a few weeks back when MySQL 8.0 was released (see my blog posts “One Giant Leap For SQL: MySQL 8.0 Released” and “Big News In Databases — Summer 2018”). Feel free to subscribe my modern SQL blog too (RSS, EMail) so you don’t miss any news.
But what I actually wanted to tell you about is my upcoming advanced SQL training in Vienna (Austria) from September 17 to 21 2018. Yes, that’s full week of SQL goodness.
The training covers the full content of my book “SQL Performance Explained” and a lot of the “modern SQL” topics I’m currently writing and talking about (window functions, recursion, …) but also barely known basics like the three-valued logic of SQL. You can find all details and register (without obligation) at my “commercial” website winand.at.
(Read more)
The Spanish translation of my book SQL Performance Explained has just been published. It’s the fifth language so far … now there’s a German, English, French, Japanese and Spanish version of the book!
You can buy it directly from the book website (PDF or paperback), and some it’s also available through some European Amazon stores (paperback only). The prices are the same as for the other languages: €9.95 for the PDF, €29.95 for the hard copy, €34.95 for PDF+paperback. If you order from the book website, you get free shipping (worldwide!)
As for the other languages, all of the Spanish content is available for free at https://use-the-index-luke.com/es/.
(Read more)
(Read more)
Don’t fall behind: Here’s the most important database news from the last six months.
(一)SQL on The Rise
(二)The Cloud War Continues
(三)IBM’s Renaming Insanity
(四)New Database Releases
(五)News on my Sites
(六)From Twitter, in Great Brevity (follow me on Twitter)
(Read more)
Here is the most noteworthy news since my last “Big News In Databases” post from November 2016.
(一)A new SQL standard was released (SQL:2016)
(二)Oracle Database 12.2 on premises, cloud prices double for AWS and Azure
(三)Database of the year
(四)ACIDRain: Study highlights security risk of wrong transaction use
(五)From my Twitter timeline
(Read more)
There’s been a lot of big news in 2016, also when it comes to databases. Let’s have a look at the really BIG news.
(一)MySQL and MariaDB Become Modern
(二)Parallel Execution in PostgreSQL 9.6
(三)SQL Server on Linux (Beta)
(四)Oracle 12cR2 Currently Cloud Only
(五)DB2 Learns OFFSET…Unfortunately
(六)SQLite Gets Row-Values
(Read more)
Translations
A Japanese translation of this article is available here.
A Chinese translation of this article is available here.
A few days ago Uber published the article “Why Uber Engineering Switched from Postgres to MySQL”. I didn’t read the article right away because my inner nerd told me to do some home improvements instead. While doing so my mailbox was filling up with questions like “Is PostgreSQL really that lousy?”. Knowing that PostgreSQL is not generally lousy, these messages made me wonder what the heck is written in this article. This post is an attempt to make sense out of Uber’s article.
In my opinion Uber’s article basically says that they found MySQL to be a better fit for their environment as PostgreSQL. However, the article does a lousy job to transport this message. Instead of writing “PostgreSQL has some limitations for update-heavy use-cases” the article just says “Inefficient architecture for writes,” for example. In case you don’t have an update-heavy use-case, don’t worry about the problems described in Uber’s article.
In this post I’ll explain why I think Uber’s article must not be taken as general advice about the choice of databases, why MySQL might still be a good fit for Uber, and why success might cause more problems than just scaling the data store.
(一)On UPDATE
(二)On SELECT
(三)On Index Rebalancing
(四)On Physical Replication
(五)On Developers
(六)On Success
(七)On Uber’s Choice of Databases
(Read more)
SQL has gone out of fashion lately—partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011. In this session, we will go through the most important additions since the widely known SQL-92, explain how they work and how PostgreSQL supports and extends them. We will cover common table expressions and window functions in detail and have a very short look at the temporal features of SQL:2011 and the related features of PostgreSQL.”
This is the abstract for the talk I’ve given at FOSDEM in Brussels on Saturday. The PostgreSQL community was so kind to host this talk in their (way too small) devroom—thus the references to PostgreSQL. However, the talk is build upon standard SQL and covers features that are commonly available in DB2, Oracle, SQL Server and SQLite. MySQL does not yet support any of those features except OFFSET, which is evil.
One last thing before going on to the slides: Use The Index, Luke has a shop. Stickers, coasters, books, mugs. Have a look.
(Read more)
I’ve just completed IBM DB2 for Linux, Unix and Windows (LUW) coverage here on Use The Index, Luke as preparation for an upcoming training I’m giving. This blog post describes the major differences I’ve found compared to the other databases I’m covering (Oracle, SQL Server, PostgreSQL and MySQL).
(一)Free & Easy
(二)No Easy Explain
(三)Emulating Partial Indexes is Possible
(四)INCLUDE Clause Only for Unique Indexes
(五)Almost No NULLS FIRST/LAST Support until 11.1
(六)SQL:2008 FETCH FIRST but not OFFSET (until 11.1)
(七)Decent Row-Value Predicates Support
(Read more)
It has been quiet here at the Use The Index, Luke blog lately. But that’s not because I’ve run out of topics to write about — in fact, my blog backlog seems to be ever growing — the recent silence is just because there are some more demanding projects happening at the moment.
First of all, Use the Index, Luke got a new mascot—not exactly breaking news. However, I’m currently preparing give-aways and merchandise products featuring the new mascot. Stay tuned.
Next, Use The Index, Luke gets translated to Japanese! The first two chapters have just been published. Insiders will remember that chapter 1 and 2 make up half of the book. The translation is done by Hayato Matsuura, Takuto Matsuu has a second look over it. As far as I can tell both are making a great job and I’d like to be the first to thank them! Please help spreading the word about this in the Japanese community.
Finally, I’m just adding DB2 as a first-class citizen to Use The Index, Luke because a client wanted to get my SQL performance training based on DB2 LUW Express-C (which is free, by the way). Like the Japanese translation, this work is not yet finished. However, the appendix on execution plans is already there. Again, please help spreading the word about this in the DB2 community.
(Read more)
Translations
[ Deutsch, Français, Português, Russian]
(Read more)
(一)Life Without OFFSET
(二)But the Frameworks…
(三)Spread the Word
(Read more)
In this guest post, Chris Saxon explains a very important topic using an analogy with chocolates: When does a database use an index and when is it better not using it. Although Chris explanation has the Oracle database in mind, the principles apply to other databases too.
A common question that comes up when people start tuning queries is “why doesn’t this query use the index I expect?”. There are a few myths surrounding when database optimizers will use an index. A common one I’ve heard is that an index will be used when accessing 5% or less of the rows in a table. This isn’t the case however - the basic decision on whether or not to use an index comes down to its cost.
(一)How do databases determine the cost of an index?
(二)What have M&M’s got to do with databases?
(三)When does an index scan become more efficient than a FTS?
(四)Bringing it all together
(Read more)
So, I’ve been to PgCon 2014 in Ottawa to give a short version of my SQL performance training. However, I think I ended up learning more about SQLite than about PostgreSQL there. Here is how that happened and what I actually learned.
(Read more)
This is my own and very loose translation of an article I wrote for the Austrian newspaper derStandard.at in October 2013. As this article was very well received and the SQL vs. NoSQL discussion is currently hot again, I though it might be a good time for a translation.
Back in 2013 The Register reported that Google sets its bets on SQL again. On the first sight this might look like a surprising move because it was of all things Google’s publications about MapReduce and BigTable that gave the NoSQL movement a big boost in the first place. On a second sight it turns out that there is a trend to use SQL or similar languages to access data from NoSQL systems—and that’s not even a new trend. However, it raises a question: What remains of NoSQL if we add SQL again? To answer this question, I need to start with a brief summary about the history of databases.
(一)Top Dog SQL
(二)And Then: NoSQL
(三)Nevertheless: Back to SQL
(四)What’s left of NoSQL?
(Read more)
Dear MySQL,
Thank you for introducing me to SQL. It must have been 1998 when we first met I and fell in love with the simplicity of SQL immediately. Before that I’ve been using C structs all the time; I had to do my joins programmatically and also create and maintain my indexes manually. It was even hard to combine several search conditions via and and or. But then there was the shiny new world of SQL you were showing me…
Everything was easily. Just write a where clause, no matter how complex, you found the right rows. Joins were equally easy to write and you took all the effort to combine the data from several tables as I needed them. I also remember how easy it became to manage the schema. Instead of writing a program to copy my data from one C struct to another, I just say alter table now—in the meanwhile it even works online in many cases! I didn’t take long until I used SQL for stuff I wouldn’t have thought a database could do for me. So I was quickly embracing group by and co.
But I haven’t spent a lot of time with you lately. It’s not because I was too busy. I’m still practicing what you have shown me! And I’ve moved on. Now I’m using common table expressions to organize complex queries and I use window functions to calculate running totals or just do a ranking. I’m also using joins more efficiently because I know about hash and sort/merge joins. A while ago I was wondering why you didn’t tell me about these things. But then I realized that you don’t know them.
(Read more)
In 2011, I’ve launched the “The 3-Minute Test: What do you know about SQL performance.” It consists of five questions that follow a simple pattern: each question shows a query/index pair and asks if it demonstrates proper indexing or not. Till today, this test has become one of the most popular features on Use The Index, Luke and has been completed more than 28,000 times.
Note
Just in case you got curious, please be aware that this article is a spoiler. You might want to do the quiz yourself before continuing.
Although the quiz was created for educational purposes, I was wondering if I could get some interesting figures out of these 28,000 results. And I think I could. However, there are several things to keep in mind when looking at these figures. First, the quiz uses the surprise factor to catch attention. That means, three questions show cases that look fine, but aren’t. One question does it the other way around and shows an example that might look dangerous, but isn’t. There is only one question where the correct answer is in line with the first impression. Another effect that might affect the significance of the results is that there was no representative selection of participants. Everybody can take the quiz. You can even do it multiple times and will probably get a better result the second time. Just keep in mind that the quiz was never intended to be used for scientific research upon the indexing knowledge in the field. Nevertheless, I think that the size of the dataset is still good enough to get an impression.
Below I’ll show two different statistics for each question. First, the average rate at which this question was correctly answered. Second, how this figure varies for users of MySQL, Oracle, PostgreSQL and SQL Server databases. In other word, it says if e.g. MySQL users are more knowledgeable about indexing as PostgreSQL users. Spoiler: It’s the other way around. The only reason I’m in the lucky position to have this data is that the test sometimes uses vendor specific syntax. For example, what is LIMIT in MySQL and PostgreSQL is TOP in SQL Server. Therefore, the participants have to select a database at the beginning so that the questions are shown in the native syntax of that product.
(一)Question 1: Functions in the WHERE Clause
(二)Question 2: Indexed Top-N Queries
(三)Question 3: Index Column Order
(四)Question 4: LIKE Searches
(五)Question 5a: Index-Only Scan
(六)Question 5b: Index Column Order and Range Operators
(七)Overall Score: How Many Passed the Test?
(Read more)
As you might have noticed—at least if you have read SQL Performance Explained—I don’t think clustered indexes are as useful as most people believe. That is mainly because it is just too darn difficult to choose a good clustering key. As a matter of fact, choosing a good—the “right”—clustering key is almost impossible if there are more than one or two indexes on the table. The result is that most people just stick to the default—which is the primary key. Unfortunately, this is almost always the worst possible choice.
In this article I explain the beast named clustered index and all it’s downsides. Although this article uses SQL Server as demo database, the article is equally relevant for MySQL/MariaDB with InnoDB and the Oracle database when using index-organized tables.
(一)The costs of an extra level of indirection
(二)How bad is it?
(三)Who cares about logical reads anyway
(四)What was the upside of clustered indexes again?
(五)Concluding: How many clustered indexes can a SQL Server table have?
(Read more)
Translations
A Japanese translation of this article is available here.
Yesterday evening I tweeted: “MongoDB seems to be as Bad for NoSQL as MySQL is for SQL.” Unfortunately, I tweeted without context. But I guess I couldn’t have given all the required context in a single tweet anyway, so I’m dedicating this post to it. I hope this answers some of the questions I’ve got in response to the tweet.
First of all, I think everybody should know that I’m not a NoSQL fanboy, yet I’m open to the polyglot persistence idea. This distinction doesn’t seem to make sense if you read NoSQL as “not only SQL” (as you are supposed to do). However, I believe there are NoSQL systems out there that greatly benefit from the idea that SQL is bad and not using SQL is good. On other words, they offer “not using SQL” as their main advantage. MongoDB seems to be one of them. Just my perception.
But if I don’t like NoSQL, then I should like MySQL? Not exactly. In my eyes, MySQL has done great harm to SQL because many of the problems people associate with SQL are in fact just MySQL problems. One of the more important examples is that MySQL is rather poor at joining because is only supports nested loops joins. Most other SQL database implement the hash join and sort/merge join algorithms too—both deliver better performance for non-tiny data sets. Considering the wide adoption of MySQL (“The most popular open source database”) and the observation that many people move away from SQL because “joins are slow,” it isn’t far-fetched to say that an implementation limitation of MySQL pushes people towards NoSQL.
(Read more)
(Read more)
Quite often I’m asked what I think about query hints. The answer is more lengthy and probably also more two-fold than most people expect it to be. However, to answer this question once and forever, I though I should write it down.
The most important fact about query hints is that not all query hints are born equally. I distinguish two major types:
Restricting Hints
Most query hints are restricting hints: they limit the optimizers’ freedom to choose an execution plan. “Hint” is an incredibly bad name for these things as they force the optimizer to do what it has been told—probably the reason MySQL uses the FORCE keyword for those.
I do not like restricting hints, yet I use them sometimes to test different execution plans. It usually goes like this: when I believe a different execution plan could (should?) give better performance, I just hint it to see if it really gives better performance. Quite often it becomes slower and sometimes I even realize that the execution plan I though of does not work at all—at least not with the database I’m working at that moment.
Typical examples for restricting query hints are hints that force the database to use or not use a particular index (e.g., INDEX and NO_INDEX in the Oracle database, USE INDEX and IGNORE INDEX in MySQL, or INDEX, FORCESEEK and the like in SQL Server).
So, what’s wrong with them? Well, the two main problems are that they (1) restrict the optimizer and that they (2) often need volatile object names as parameters (e.g., index names). Example: if you use a hint to use index ABC for a query, the hint becomes ineffective when somebody changes the name of the index to ABCD. Further, if you restrict the optimizer you can no longer expect it to adjust the execution plan if you add another index that servers the query better. Of course there are ways around these problems. The Oracle database, for example, offers “index description” hints to avoid both issues: instead of specifying the index name, it accepts a description of the ideal index (column list) and it selects the index that matches this definition best.
Nevertheless, I strongly recommend against using restricting query hints in production. Instead you should find out why the optimizer does “the wrong thing” and fix the root cause. Restricting hints fix the symptom, not the cause. That being said, I know that there is sometimes no other reasonable choice.
Supporting Hints
The second major type of query hints are supporting hints: they support the optimizer by providing information it doesn’t have otherwise. Supporting hints are rare—I’m only aware of a few good examples and the most useful one has already become obsolete: it’s FAST number_rows (SQL Server) and FIRST_ROWS(n) (Oracle). They tell the optimizer that the application plans to fetch only that many rows of the result. Consequently, the optimizer can prefer using indexes and nested loop joins that would be inefficient when fetching the full result (see Chapter 7, Partial Results for more details). Although being kind-of obsolete, I’m still using these hints as the defining example for supporting hints because they provide information the optimizer cannot have otherwise. This particular example is important enough that it was worth defining new keywords in the ISO SQL:2008: FETCH FIRST ... ROWS ONLY and OFFSET. That’s why this hint is a very good, yet obsolete example for supporting query hints.
Another example for supporting hints is the (undocumented) CARDINALITY hint of the Oracle database. It basically overwrites the row count estimate of sub-queries. This hint was often used if the combined selectivity of two predicates was way off the product of the selectivity of each individual predicate (see “Combined Selectivity Example”). But this hint is also outdated since Oracle database 11g introduced extended statistics to cope with issues like that. SQL Server’s filtered statistics serve the same purpose. If your database cannot reflect data correlation in it’s statistics, you’ll need to fall back to restricting hints.
The Oracle hint OPT_ESTIMATE is somehow the successor of the CARDINALITY hint for cases when the estimations are still off. Pythian wrote a nice article about OPT_ESTIMATE.
Combined Selectivity Example
Let’s say we have two Y/N columns and each has a 50:50 distribution. When you select using both columns most optimizers estimate that the query matches 25% of the table (by multiplying two times 50%). That means that the optimizer assumes there is no correlation between those two columns.
| Column 1 | Column 2 | count(*) |
| Y | Y | 25 |
| Y | N | 25 |
| N | Y | 25 |
| N | N | 25 |
| Column 1 | Column 2 | count(*) |
| Y | Y | 49 |
| Y | N | 1 |
| N | Y | 1 |
| N | N | 49 |
It think supporting hints are not that bad: they are just a way to cope with known limitations of the optimizer. That’s probably why they tend to become obsolete when the optimizers evolve.
(Read more)
Here is another slide deck for my “Pagination Done the Right Way” talk that I’ve given at many occasions.
Please also have a look at this blog post by Cary Millsap about “The Ramp”. Do you see how using OFFSET implements this anti-pattern?
(Read more)
I think I’ve actually never shared the slides of my talk given in Paris at Dalibo’s PostgreSQL Session about Performance. So, here they are.
(Read more)
(Read more)
(Read more)
(Read more)
Hi!
Would you please spare me a few minutes and help me a little bit?
As you might know—or maybe not—I’m making my living as an independent trainer and consultant. Up till now I’ve only delivered on-site training at the clients’ site, but I though it makes sense to offer open trainings as well so that singe participants can also join. For that I’d need to know how many people would like to join such a training, where they are physically located, and which database they are using. So, I’ve set up a short survey:
http://winand.at/services/sql-performance-training/survey
(Read more)
(Read more)
(Read more)
(Read more)
(Read more)
(Read more)
(Read more)
(Read more)
You might guess it, but I’m not making my living from this site nor are the book sales paying all the bills—although the books pays some bills, thank you! However, to pay the remaining bills I’m doing coaching, training and consulting. You can find all the details on my professional site http://winand.at/ but I’d like to highlight one service here: Instant Coaching.
After googling and trying many ways to solve a problem, have you ever got that feeling of resignation where you just want to ask somebody who should know the answer? Well, that’s Instant Coaching—at least if your problem is about performance and/or databases.
Instant Coaching is remarkably easy and efficient: we just arrange an online meeting where you present your problem or question to me. We’ll use a desktop sharing tool (TeamViewer) so that I can see your screen and—if you like—show you something on your computer (control your mouse/keyboard). Instant Coaching needs virtually no preparation: it often starts just a few minutes after you faced the problem—hence “Instant”!
Another critical success factor for Instant Coaching is that we can put our knowledge and experiences together: you bring the know-how about your application and your data while I’m adding a little database and performance know-how to the mix. Instead of guessing about things I don’t know about your application or you don’t know about databases, we can just ask each other! I’m usually getting asked a lot: you don’t just get the “magic” solution, but you learn about the rational behind it so that can solve similar problems on your own the next time—hence “Coaching”!
(Read more)
(Read more)
With a little delay of just three weeks, I’m writing something about this years PostgreSQL conference Europe.
Including the training day, it was four days full of database stuff—in other terms: an awesome time, if you love databases :) But let’s start from the beginning.
I booked the full day training with Joe Celko on the first day. And I must say that was the best part of the conference—mostly because Joe is great. If you never meet Joe Celko, or don’t even know who the heck he is, you should definitely look for an opportunity to meet him.
The training he gave (“A day of SQL with Celko”) was a mixture of SQL related topics like keys, naming (e.g., plurals for tables), metadata, rollup (group by grouping sets, group by cube) and trees. Due to my day to day work, I’ve already seen some of the “rarely known features” he mentioned. It was still very valuable because he presented good examples for them. The “nested sets model” is particularly noteworthy. I hope to write about some of these topics soon.
(Read more)
(Read more)
As mentioned before, I’ll be at this year PostgreSQL Conference in Prague. Still two weeks to go, but I just had a few minutes “free” time and prepared my personal schedule.
As usual, I plan to attend as many sessions as possible. As usual, I’ll skip some in favour of a nice chat and/or change my mind in the last moment.
(Read more)
“Use The Index, Luke!” celebrates its second anniversary today. Unfortunately, I was not able to prepare anything for this anniversary like I did last year when I presented the 3-minute test. The test is still very popular, by they way, it is taken about 20 times a day. However, all I have for this years anniversary is a promise.
Do you know http://sqlfiddle.com/? You should ;) It’s an online tool to test SQL queries with five different databases (SQL Server, MySQL, Oracle, PostgreSQL and SQLite). It is not a cloud database that you could use for building applications. SQLFiddle is a tool for collaborative online work on SQL problems. It was built to improve asking and solving SQL related questions on Q/A sites like Stackoverflow.
For me, SQL Fiddle is interesting because of the “View Execution Plan” Button. As said before, it is not a cloud DB that we could use for performance tests, but we can have a look at executions plans. I believe it would be very convenient to have prepared fiddles for the examples from “Use The Index, Luke!” so that you can easily test them online without needing your own database.
And this is exactly what will come—although I don’t have a time line yet. Stay tuned. UPDATE in 2015: SQLFiddle integration will be removed again because it it hardly used but causes a lot of overhead on my side.
(Read more)
After publishing the English edition of “SQL Performance Explained” there was a huge demand for an ebook edition. After a few days answering these queries, I though it’s probably easier to offer an e-book than to explain why there isn’t one. Well, I must admit that I don’t read ebooks very much. My wife has an e-ink device, but is hardly using it (probably in lack of German content). I’ve uploaded the ePUB pre-release from 2011 (not available anymore) and must say it works, but isn’t nice. However, many people told me they would be fine with a PDF, because they are anyway using tablets or smartphones to read their ebooks. Fast forward a few weeks: since Monday I can offer a PDF download for just EUR 9.95 (English, German [update] or French, you choose).
Afterwards there was a huge demand—again—for a bundle discount when purchasing both, the PDF and the hard copy. That caught me by surprise. Not that people want a discount, but that people would like to buy both editions. So I asked, and listened, and learned something about my own business.
Sure there were usual suspected like “I’d just like the PDF for searching” “to have it with me when being on the road” and so forth. But the interesting point was “I’d like the hard copy, but would also like to start reading immediately.” Well, I didn’t think of that (shame on me). Although I’m offering free shipping to 150+ countries, it takes its time because I’m shipping from Europe only once a week. Instead of express delivery, I just need to offer a bundle.
It took another day to tweak my accounting software because there are different VAT rates applied to real books and PDF downloads—but the accounting software could do only one VAT rate for each invoice. After all I’m just a geek like most of you, so I hacked a day and made it work. The bundle is on sale for EUR 34.95 since yesterday evening—that’s a EUR 4.95 discount.
(Read more)
(Read more)
The English edition of “SQL Performance Explained” is finally available. It has its own website http://sql-performance-explained.com/ where you can find a PDF preview and order information. The cover price is EUR 29.95 or GBP 26.99.
If you order it from outside the EU, you must also consider that you might be required to pay import duties in the destination country.
Knowing that I have readers all over the world, I’ve spent a fair amount of time to make world-wide free shipping possible. I almost succeeded. If you use the direct order form I’ll be able to offer free shipping to 150+ countries. However, this is only possible when using a volume discount offered by the Austrian postal service. Therefore, I’ll dispatch international orders only once a week.
SQL Performance Explained
(Read more)
During the last few weeks, it has become a little silent here at Use The Index, Luke! Today, I’d like to tell you the news that caused the silence and give a short outlook about the future of Use The Index, Luke!
(Read more)
Use The Index, Luke! was one today.
There was quite some progress during this year. Besides the steadily growing content, I am particularly happy that the appendices about execution plans and the example schema cover MySQL, PostgreSQL and SQL Server in addition to Oracle.
But what’s next?
The content will be completed until early 2012 (final outline in the Preface). The second volume of SQL Performance Explained—the e-book edition—will be published at that time as well. Additionally, Use The Index, Luke! will be translated gradually into German.
(Read more)
Update 2017-05-23
MariaDB 10.2 introduced support for common table expressions.
Update 2018-04-19
MySQL 8.0 introduced support for common table expressions.
A row generator is a method to generate numbered rows on demand. It is possible to build a row generator with pure standard SQL—using recursive common table expressions (CTE), the with clause. If you never heard of the with clause, it’s probably because MySQL doesn’t implement that specific part of the SQL-99 standard (feature request from 2006). This article introduces generator views for MySQL. Not as powerful as recursive CTEs, but good enough in most cases. But before going into the implementation details, I’ll show you a use case for a row generator.
Row generators are useful to fill gaps in results. Consider the following query:
(Read more)
(Read more)
(Read more)
You can’t learn everything in one day. Subscribe the newsletter via E-Mail, TwitterorRSS to gradually catch up. Have a look at modern-sql.com as well.
About the Author
Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.
Buy the Book
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Hire Markus
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »
Expert, competent, or just a greenhorn? Test your SQL performance skills in just three minutes. ﹀
●Preface
●Anatomy of an Index
●The Where Clause
●Performance and Scalability
●The Join Operation
●Clustering Data
●Sorting and Grouping
●Partial Results
●Modifying Data
●Execution Plans
●Myth Directory
●Example Schema
●Glossary
●A Close Look at the Index Include Clause
●Upcoming Training: SQL Performance Kick-Start and modern SQL
●Spanish Anyone?
●News Review: Cloud Wars, IBM’s Renaming Insanity, and Three Major Releases
●Big News: new SQL standard, cloud wars, ACIDRain
●Big News in Databases
●On Uber’s Choice of Databases
●Modern SQL in PostgreSQL [and other databases]
●Seven Surprising Findings About DB2
●Stickers
●Coasters
●Books
●Mugs
Connect with Markus Winand
Subscribe mailinglistsMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Bluesky
Copyright 2010-2024 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR
