How can I get the total rows for pagination within the least amount of processing time?
My select SQL in products list is too complex for showing the total pages in pagination. I have to query the SQL with no limit parameter which takes too much time to process. Is there a better way to get the total records?
This is my SQL:
SELECT DISTINCT
p.products_model,
p.products_image,
pd.products_name,
p.products_quantity,
p.products_id,
p.products_type,
p.master_categories_id,
p.manufacturers_id,
p.products_price,
products_price_market,
p.products_tax_class_id,
pd.products_description,
IF (pbs.products_id, 1, 0) pdsid,
IF (
s. STATUS = 1,
s.specials_new_products_price,
NULL
) AS specials_new_products_price,
IF (
s. STATUS = 1,
s.specials_new_products_price,
p.products_price
) AS final_price,
p.products_sort_order,
p.product_is_call,
p.product_is_always_free_shipping,
p.products_qty_box_status,
p.products_recommend,
p.products_in_stock
FROM
jy_products_description pd,
jy_products p
LEFT JOIN jy_products_best_selling pbs ON p.products_id = pbs.products_id
AND pbs.category_id = 77,
jy_products_to_categories p2c
LEFT JOIN jy_specials s ON p2c.products_id = s.products_id
AND s. STATUS = 1,
jy_products_attributes pa0
WHERE
p.products_status = 1
AND p.products_quantity > 0
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND p.products_id = pa0.products_id
AND pd.language_id = 1
AND p2c.categories_id = 77
ORDER BY
pdsid DESC,
pbs.sort_by ASC,
p.products_ordered