For fixing the actual queries, you are at the mercy of Joomla.
"Nearest" is a tough problem.
8000 real (though transient) tables is not bad. Recommend you add a PRIMARY KEY that is AUTO_INCREMENT as you create them. Then use the id for pagination -- it will be sooooo much more efficient than LIMIT and OFFSET.
CREATE TABLE user_1234_tmp (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
)
SELECT ... FROM ...
WHERE messy-filtering-and-range-stuff
ORDER BY the-order-you-want
This will do your costly SELECT, but put it into a very efficient temp table.
Then, the "Next"/"Prev" buttons will send in the desired page number ($page = $_GET['page']) for the next/prev page. Suppose you want 10 items per page; then the query for page $page is
SELECT ... FROM user_1234_tmp WHERE id > 10*$page ORDER BY id LIMIT 10;
The first page will be slow, as you already know. The subsequent pages will be very fast.
You might put all such tables in a separate database for ease of organizing them, and for ease of purging if some get left behind.
Also, since they are transient tables, MyISAM is fine; InnoDB is probably not necessary. You should probably drop any such tables as part of startup. (Simply DROP DATABASE and CREATE DATABASE, assuming you follow my prev suggestion.)
The MEMORY Engine is not recommended for these tables:
(1) you won't get much extra speed, and
(2) you could overflow available RAM.
Opinion: Allowing the user to page through 10K items is unreasonable.