douchuituo3032 2013-07-09 03:25
浏览 39
已采纳

如何在最少的处理时间内获得分页的总行数。

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

enter image description here

  • 写回答

1条回答 默认 最新

  • duanre4421 2013-07-09 04:02
    关注

    First use

    SELECT *
    FROM table
    WHERE
    STATUS = 1 AND
    catagory = 100
    HAVING
    quantity > 0
    ORDER BY
    sort ASC
    

    This will gain the full Query using PDO you can just COUNT the Amount you have in Total. Store that in the Session, and then finally use this.

    SELECT *
    FROM table
    WHERE
    STATUS = 1 AND
    catagory = 100
    HAVING
    quantity > 0
    ORDER BY
    sort ASC
    LIMIT #, #
    

    Combined with your Total Count, you can Divide the Count by the # Of Items your Showing on the Page. With that you can then use that to determine the number of pages. Round up of course. then simply use go about the amount limit, on each paganation that you stored in the session on your first run, this way you dont kill your calls constantly.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 机器学习教材中的例题询问
  • ¥15 求.net core 几款免费的pdf编辑器
  • ¥15 C# P/Invoke的效率问题
  • ¥20 thinkphp适配人大金仓问题
  • ¥20 Oracle替换.dbf文件后无法连接,如何解决?(相关搜索:数据库|死循环)
  • ¥15 数据库数据成问号了,前台查询正常,数据库查询是?号
  • ¥15 算法使用了tf-idf,用手肘图确定k值确定不了,第四轮廓系数又太小才有0.006088746097507285,如何解决?(相关搜索:数据处理)
  • ¥15 彩灯控制电路,会的加我QQ1482956179
  • ¥200 相机拍直接转存到电脑上 立拍立穿无线局域网传
  • ¥15 (关键词-电路设计)