Ok, I need to load all rows from a table using SELECT on a certain condition, however the amount of rows that get returned from the query for this table is over 20 thousand and it will grow much larger in the future to well over that, as it's the main table where all the data is stored. I thought about breaking it up some, but I believe it's broken up the best way that it can be broken up currently.
Basically, I need a quick, efficient way to present this data to the client. That is, ALL of the ROWS at once. I am outputting these rows within a table using tr elements, and using wordpress $wpdb like so:
$items = $wpdb->get_results("
SELECT qvi.ItemName AS name, qvi.TimeAdded AS created, qvi.EffectiveDate AS effective, qvi.VendorName AS supplier, qvi.Source AS source, qvi.Type AS type, qvi.PurchaseCost AS cost, qvi.SalesPrice AS price, IFNULL(qe.CustomerRef_FullName, 'N/A') AS customer, qvi.VendorListID AS vendorListID, qe.CustomerRef_ListID AS customerListID, qvi.ItemListID AS itemListID
FROM " . $wpdb->prefix . "quickbook_vendor_items AS qvi
LEFT JOIN " . $wpdb->prefix . "quickbook_estimates AS qe ON (qe.id = qvi.EstimateID)
WHERE qvi.IsActive = 1 ORDER BY qvi.ItemName ASC", ARRAY_A);
The Main Problem is that it takes about 5 minutes to load all of the data from this table with the above query, and well, I need a better way to do this. Wondering on how others have approached this, that have run into a similar issue with too much data from a query, and how they outputted this data?
Basically, I am open to any ideas that can help make this be a better experience to the client: Partial loading? Loading into a jQuery plugin perhaps? Ajax Loading? Caching the data somehow?
What's the best approach to accomplish this and why?
