Blog

Published by Jordan Raine on Feb 17, 2019 4:09:05 PM

Using FORCE INDEX: When Your Query Optimizer Gets It Wrong

A Guest Post from Jordan Raine, Clio

Jordan Raine is a staff software developer at Clio, the leading provider of legal practice management, client relationship management, and client intake software. Clio is dedicated to helping lawyers meet client needs and run efficient, profitable practices. Jordan wrote this post to help mySQL developers save time/effort and ultimately improve their customers' experience. 

Read More
Published by Baron Schwartz on Feb 8, 2019 2:45:23 PM

Inspecting Index Usage In MySQL, PostgreSQL, and MongoDB

In my recent post about the Left-Prefix Index Rule, I explained how queries can use all or part of a compound (multi-column) index. Knowing what makes an index fully usable by a query is important, but it's also important to know how to figure out how much of an index a query is able to use. In this article I'll show you how to do this by reading the query's explain plan. This article covers MySQL, Postgres, and MongoDB.

Read More
Published by Baron Schwartz on Feb 5, 2019 11:29:13 AM

The Left-Prefix Index Rule

There's an important heuristic in database indexing that I call the left-prefix rule. It helps you understand whether the database will be able to make the best use of a multi-column index to speed up a query. It's one of a small handful of really important things to understand about indexes!


The left-prefix rule can be confusing, but once you grok it, it's simple and easy to use. It applies to MySQL, PostgreSQL, MongoDB, and many other databases. In this post I'll explain what the left-prefix rule means and how it works.

Read More
Published by Baron Schwartz on Feb 1, 2019 11:04:00 AM

Covering Indexes in MySQL, PostgreSQL, and MongoDB

A covering index is a fantastic query performance optimization. An index covers a query when the index has all the data needed to execute the query, so the server can retrieve the query’s data without reading any rows or documents. Covered queries are usually SELECT queries, but in addition to reads, indexes can cover portions of what a write query needs to do its work.

In this article I’ll explain what a covering index is in a bit more detail; the technical implementation and caveats in MySQL, PostgreSQL, and MongoDB; and how to check that a query is using a covering index.

Read More
Published by Baron Schwartz on Apr 26, 2018 12:37:35 PM

The High Cost and Low Benefit of Unused Index Advice

You have to be careful what you wish for. The signal:noise ratio of finding out which indexes are “unused” is low. This isn’t obvious at first glance, but upon deeper inspection, there’s often not much to be gained, and it can even harm you. Let’s look at why.

 

Read More
Published by Baron Schwartz on Sep 21, 2017 4:31:49 PM

Find Queries That Need Indexes With VividCortex

Queries can be slow or resource-heavy for many reasons, but one of the most common is that the table doesn't have a good index for the query. This is true for MySQL, PostgreSQL, and especially for MongoDB. Adding an index where it's missing is often a night-and-day improvement, as shown in this screenshot of a query's performance. The index was added midway through the time range, and the query got dramatically faster:

Read More

Recent Posts

Posts by Topic

see all