We never select more than the LIMIT rows, all we do is use SQL_CALC_FOUND_ROWS in the main query, which tells us how many rows would have been selected without the LIMIT. It's a MySQL feature specifically designed for handling display pagination, so you don't have to do a separate query to count the total rows.
However ... SQL_CALC_FOUND_ROWS can be slow on very large tables, especially if you have joined data. Do you have any list joins? If you do have joined data, you can disable the use of SQL_CALC_FOUND_ROWS by setting the joins to "merge" mode.
Another thing you can try is turning off use of DISTINCT in the advanced list settings.
Another thing to watch for is if you have any join elements which you use as list filters. Setting those to "all data" mode (as opposed to "recorded data") can speed things up on large lists.
It's also possible that you may be missing some indexes. We do our best to create indexes for any fields that need them, for example anything used in an ORDER BY or GROUP BY, or anything used in a join element, or anything used as a filter, etc. But in certain corner cases you may need to create an index or two by hand. The ony way to find that out is to capture the main list query, by enabling Fabrik Debug (in the global options) and appending &fabridebug=1 to your list URL, then you'll see the main query as the getData debug output. Copy and paste that into phpMyAdmin (or whatever) and "explain" it, which will show you if there are any missing indexes.
-- hugh