The board index.
Hmm, no, I don't see the issue..?
SELECT something
FROM table
{query_enter_board}
WHERE condition
And {query_enter_board} = JOIN boards AS b ON (b.id_board IN {list_of_boards_I_can_enter}...
No? Isn't that just as simple as having WHERE condition AND {query_enter_board}...?
I know that I used something similar in Noisen (you still have the diff patch), and it felt natural to me.
What you end up doing is an INNER JOIN to something else. OK, let's figure out what that would actually give you. Something simple like the last 5 topics in all the boards we can see. (Expanding it to actually encompass this above)
SELECT t.id_topic, b.name
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}boards AS b ON (b.id_board IN (1,2,3,4,5,6))
WHERE t.approved = 1 AND b.id_board = t.id_board
ORDER BY t.id_topic DESC
1 SIMPLE t ref approved,id_board,last_message_pinned,board_news approved 1 const 1 Using where; Using filesort
1 SIMPLE b eq_ref board_id board_id 3 wedge.t.id_board 1
As opposed to
SELECT t.id_topic, b.name
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
WHERE b.id_board IN (1,2,3,4,5,6) AND t.approved = 1
ORDER BY t.id_topic DESC
1 SIMPLE t ref approved,id_board,last_message_pinned,board_news approved 1 const 1 Using where; Using filesort
1 SIMPLE b eq_ref board_id board_id 3 wedge.t.id_board 1
This is not a perfect example, performance it's mostly a push because the join doesn't actually benefit you in any way whatsoever. The ONLY way that join will ever be a benefit (and the context in which I actually meant that comment) is if you're not doing it this way.
The bottleneck in these queries is not the method of parsing or injection. It's the fact you're still doing what amounts to WHERE id_board = 1 OR id_board = 2 OR id_board = 3 etc. It doesn't matter a pair of fetid dingo's kidneys that it's been rewritten to the JOIN because it still has to be evaluated as that to actually perform the join.
When I referred to JOINs being faster, they are faster when you're dealing with tables, rather than injected variables like that. Hell, even a join to a subquery might be slightly faster if evaluating the selection criteria there.
Isn't that just as simple as having WHERE condition AND {query_enter_board}...?
No, it gets more complicated. Plugin authors cannot rely on {query_see_board} actually joining {db_prefix}boards AS b, because it wouldn't for admin users. Which means you either have to force that extra join for admin users (as opposed to bypassing it entirely for performance), or you have to join the table manually yourself to get things like the board names.
Which means that to reliably get board names you'd actually have to do this:
SELECT t.id_topic, b2.name
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}boards AS b ON (b.id_board IN (1,2,3,4,5,6)) <------- this is the query_see_board line
INNER JOIN {db_prefix}boards AS b2 ON (b.id_board = t.id_board)
WHERE t.approved = 1 AND b.id_board = t.id_board
ORDER BY t.id_topic DESC
And then it starts to hurt, doing two joins to the same table instead of doing the correct WHERE clause.
Like everything it's relative to context. Certain cases will outperform others.
