« first day (2928 days earlier)   

4:41 PM
@TomJNowell "Passion" or "Dedication" might fit better then :)
 
5:32 PM
@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
 
5:37 PM
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?
 
5:40 PM
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
 

« first day (2928 days earlier)