query-optimization – Page 11 – Türker Yıldırım

with() eager loads now; load() does it afterwards

NoteDecember 21, 20162 min read

Both solve the same N+1 problem and they apply at different moments, which decides whether the collection you already have can be fixed or has to be re-fetched.…

Index hints are a last resort with a shelf life

NoteNovember 17, 20161 min read

FORCE INDEX makes the immediate problem go away and freezes a decision the optimiser would otherwise revisit as the data changes. It is a fix with an expiry…

The analyzer decides what matching even means

NoteOctober 25, 20161 min read

A search that fails to find an obvious result is almost never a query problem. Text is tokenised and normalised at index time, and the query goes through…

The N+1 queries you did not write

Deep diveJune 3, 20168 min read

1,400 queries on a page showing 40 rows. Eager loading, the difference between with() and load(), and the accessor that no eager load can fix.

Moving catalogue search off MySQL and onto Elasticsearch

Deep diveMay 18, 201615 min read

LIKE cannot use an index and everyone knows it. The mapping, the analyzers that decide what matching means, the indexing pipeline, and the drift it introduces.

Finding the 200 milliseconds nobody could account for

Deep diveMarch 31, 20168 min read

The page took 340ms and the parts added up to 140. Measuring from the first byte nginx sees rather than from the framework boot, and what was hiding…

no_found_rows removes a COUNT query you are not using

NoteDecember 16, 20151 min read

Every WP_Query runs a second query to count the total number of matching rows, so that pagination can be rendered. If the query is powering a sidebar widget…

The slow query log costs less than you think to leave on

NoteNovember 17, 20151 min read

The slow query log is usually described as a debugging tool to switch on when there is a problem, which means it is off at exactly the moment…

Reading EXPLAIN until a slow catalogue query gets fast

Deep diveOctober 30, 20158 min read

4.2 seconds on 60,000 rows. Following EXPLAIN from a full table scan to a covering index, and why the composite index that existed was never used.

Filtering a WooCommerce catalogue without the meta tables

Deep diveOctober 6, 201516 min read

Five filters over 30,000 variations, and a request that never returns. Replacing EAV meta joins with a denormalised index table kept in sync by product hooks.