Optimize result page to query number of result first

Question

Here is my code. But I have some problem about loading time for result more than 1000 rows.

How can I change code to check number of query result for create paginate first. Then, get result only that paginate page?

function showResult(req, res){

    var n = req.query.query;
    mysql_conn.query('SELECT query_text FROM catalogsearch_query WHERE query_text LIKE "%' + n + '%" ORDER BY popularity DESC LIMIT 0 , 10', function (error, rows) {
        mysql_crawl.query('SELECT prod_name, full_price, discount_price, quantity, fulltext_id,prod_link, images, prod_desc, status, web_name,web_logo FROM `catalogsearch_fulltext` WHERE MATCH(data_index) AGAINST("'+n+'") ', function(error, product_data) {

            var totalItems = product_data.length, itemts=product_data;

    //set default variables
    var totalResult = totalItems,
        pageSize = 10,
        pageCount = Math.floor(totalResult / pageSize)
        currentPage = 1


    //set current page if specifed as get variable (eg: /?page=2)
    if (typeof req.query.page !== 'undefined') {
        currentPage = +req.query.page;
    }

    //render index.ejs view file
            res.render('result.html', {result: n, 
            related: rows.map(row => row.query_text), 
            page_num: p,
            product_data: product_data,
            totalItems: totalItems,
            pageSize: pageSize,
            pageCount: pageCount,
            currentPage: currentPage})
        });
    });
}

score 2 · Accepted Answer · answered May 10 '17 at 10:30

first you get the total rows of the result

SELECT COUNT(*) FROM table WHERE 1 = 1;

second is the query to get the rows result but limit by ex. 10 and offset starts at zero first

SELECT * FROM table WHERE 1 = 1 LIMIT 10, 0;

it means the result will start at 0 then for the next page you should add 10 for your offset

SELECT * FROM table WHERE 1 = 1 LIMIT 10, 10;

so it starts at 10 up to 20

score 0 · Answer 2 · edited May 23 '17 at 12:26

So actually two sides: client side and server side.

Client side, using angularjs, please evaluate to use the angular ui pagination directive:

https://github.com/angular-ui/bootstrap/tree/master/src/pagination

Here a link to a stackoverflow post where there is a working example of it:

How to do paging in AngularJS?

About your server side, you could provide a way to make your query dynamic, retrieving just results in the range you pass on it. So something like that:

'SELECT query_text FROM catalogsearch_query WHERE query_text LIKE "%' + n + '%" ORDER BY popularity DESC LIMIT %, %'

where you pass your limit parameters from the client according to the pagination. So before to load a new page, you call the query with the right LIMIT parameters

Optimize result page to query number of result first

2 Answers2