@TomJNowell "Passion" or "Dedication" might fit better then :)

@TomJNowell I've noticed that you mention that NOT IN will slow the query drastically. But isn't a database query smart enough to simply skip a record whenever it matches the NOT IN and simply go to the next record? That shouldn't take more time that doing it via PHP

@JackJohansson the problem is that if it's in a situation where it can just skip over a row like that, then you've already lost

when you do a database query, it's usually fast because of indexes

it's the difference between scanning every page in a book 1 by 1 for a title from beginning to end, versus looking it up in a table of contents you prepared earlier

that's why querying for post meta using a post ID is super fast, or why filtering by a posts date or status is fast

those have indexes and various other optimisations applied

So, it could fetch x + y results and then drop the extra rows

but for post__not_in that leads to 2 possible scenarios

That should be exactly how it's done via PHP

1: A full table scan, checking each row individually

2: A temporary table gets built by scanning the real table, and applying the conditions. Then, a simplified version of the query is done on the temporary table, and the result returned, followed by the temporary table being discarded

e.g. if you want to do multiple calculations or different clauses, it will apply those calculations and store the result in a new table, then do the same query minus the calculation on the new table

but the general issue, is the not part

you always want to ask a DB for what you want, never for what you do not want

they're built and optimised for finding things

not excluding or hiding

that's why a post meta key named show_on_homepage is always faster than hide_from_homepage

I don't get it. Both MySQL and PHP are written in C. If PHP can do it fast in machine level, then so can MySQL

not true

When you have the data in PHP it's a simple conditional against an ID, MySQL has already done the heavy lifting

and MySQL isn't optimised for those types of queries

If you had to do the entire table row scanning thing in PHP, it too would take a long time

Why can't MySQL do the simple conditional right before returning the result?

then there's the tradeoffs of how databases store their data

you'd have to ask a database expert that

but suffice to say, it's not that simple

nor is it always fastest, there are other concerns

for example, what if you want 2 posts, but exclude 1000?

I'll probably get a "Not needed" answer if I ask it

how many posts should your query return, and if you have pagination, does that answer stay the same?

Also not specifying the NOT IN will ruin the pagination

if we're doing a naive answer, the DB engine would need to return 1002 posts just in case the first 1000 were the excluded posts

and you'd need to know how many of the excluded posts were in previous pages, as well as where they were found to do pagination

hence the temporary tables

or the table scans

eitherway no such scans take place in most queries

so it doesn't need to check the row, that was done long before

Every scenario is complicated. Probably should just categorize your stuff properly and then query for what you need

indeed, that's the point

but sometimes you need to avoid outputting a post more than once, or excluding something, so do it in PHP

Yes it'll work for simple situations, but I guess the ideal way is to setup your database schema property before you even start a database

Otherwise every solution is nasty