post inclusion, a solid WHERE clause filter before any JOIN on SELECT post (listing of posts)

Sorry about the slow response. What I mean is, suppose you have a column with 1000 integers (27,5,100,60,...). You want to print the top 10:

 undefined

        SELECT num FROM xyz ORDER BY num DESC LIMIT 10;


  

The db has to scan all 1000 rows to find the 10 biggest numbers. Now suppose instead there is an index on that column, i.e. a btree that lets you search for a value with very few operations, or traverse the list in order. Now the SELECT doesn't have to examine all the rows. It only has to traverse 10 items from the index, starting at the large end. It does mean that UPDATE and INSERT operations for that columb become more expensive, since the index has to be updated too, but that too is less expensive than a table scan.

I'm saying that by having similar indexes on the possible sorting orders of read queries, you can likewise get rid of all the table scans. Does that make sense?

Similarly if you JOIN two indexed fields, that is like merging two sorted lists. The db can traverse both indexes in parallel to find the matching values. Db's can be very clever about stuff like this. It helps though if you use EXPLAIN to make sure they are doing the right thing.