novacreative
Member
I know that &order_by is an option in a querystring. How about &limit=?
I'm dealing with a huge database, and it sure would be nice.
I'm dealing with a huge database, and it sure would be nice.
-
New Commercial Services Section
We have now opened a commercial services section here on the forum for registered users. If you have a Fabrik project that you wish to have someone work on for you, post it under Help Wanted. If you are an application developer and wish to earn some money helping others, post your details under Fabrik Application Developers.
Both of these are unmoderated. It will be up to both parties to work out the details and come to an agreement.
-
Joomla 5.1
For running J!5.1 you must https://fabrikar.com/forums/index.php?wiki/update-from-github/ or include the new file manually https://fabrikar.com/forums/index.php?threads/joomla-5-1-and-fabrik-cannot-find-files-error.54473/post-285151 See also Announcements
Querystring with LIMIT?
- Thread starter novacreative
- Start date
cheesegrits
Support Gopher
That should be handled automatically for any list display, using the nav limits specified in the list settings. We don't do any unonstrained queries. What is the specific scenario you need to specify your own limits for?
-- hugh
-- hugh
novacreative
Member
That does work to just present a limited number of results. My problem is that I'm dealing with a very large number of records, nearly a million. I'm trying to access it around the edges to keep load times from becoming infinite. It seems when I use the rows per page and no nav buttons solution, the site is still taking a long time to organize pages of results even though they won't be displayed. I thought that if I could order and limit the search to a reasonable number of results by means of the querystring, I might be able to get around this issue.
cheesegrits
Support Gopher
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
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
novacreative
Member
Many thanks. That's very helpful info. I will try what you've suggested and let you know the results.