CODEWITHSUNDEEP
phpmysqlindexing
Tuz
Tuz

Reputation: 1980

Pagination query mysql+php take 20-40 seconds

Hi I have pagination in angular js in my app, I send the data to my big query that includes the filters that the user set

UPDATE SQL_CALC_FOUND_ROWS this is my problem. How do I count the rows of specific filters . It is took me 2 second for 100,000 rows I need the number for the pagination as a total number

UPDATE: I have the following inner query that I missed here :

(select count(*) from students as inner_st where st.name = inner_st.name) as names,

when I remove above inner query is much faster

rows: 50,000 Users table : 4 rows Classes table : 4 rows indexes: only id as primary key

query time 20-40 seconds

tables: students.

columns : id, date ,class, name,image,status,user_id,active 

table user
coloumn: id,full_name,is_admin

query

SELECT  SQL_CALC_FOUND_ROWS st.id, 
        st.date,
        st.image,
        st.user_id,
        st.status,
        st, 
        ck.name AS class_name,
        users.full_name,
        (select count(*) from students AS inner_st where st.name = inner_st.name) AS names,
FROM students AS st
    LEFT JOIN  users ON st.user_id = users.user_id
    LEFT JOIN  classes AS ck  ON st.class = ck.id
WHERE date BETWEEN '2018-01-17' AND DATE_ADD('2018-01-17', INTERVAL 1 DAY)
    AND DATE_FORMAT(date,'%H:%i') >= '00:00'
    AND DATE_FORMAT(date,'%H:%i') <= '23:59'
    AND st.active=1 
    -- here I can concat filters from  web like "and class= 1"
ORDER BY st.date DESC
LIMIT  0, 10

How can I make it faster? when I delete the order by and SQL_CALC_FOUND_ROWS it faster but i need them I heard about indexes but only primary key is index

Upvotes: 0

Views: 173

Answers (2)

Rick James
Rick James

Reputation: 142443

Give this a try first:

INDEX(active, date)

Is user_id the PK for users? Is class_id the PK for classes? If not, then they should be INDEXed.

Why are you testing the times separate?

Fix the test so it is obvious which table each column is in.

Do you really need LEFT JOIN? Or would JOIN suffice? In the latter case, there are more optimization options.

Give some realistic examples of other SELECTs; different index(es) may be needed.

Is the "first" page slow? Or only later pages? See this for pagination optimization -- by not using OFFSET.

Upvotes: 0

Tomer Shay
Tomer Shay

Reputation: 801

Few comments before recommending a different approach to this query:

  • Did you consider removing SQL_CALC_FOUND_ROWS and instead running two queries (one that counts and one that selects the data)? In some cases it might be quicker than joining them both to one query.
  • What is the goal of these conditions? What are you trying to achieve? Can we remove them (as it seems they might always return true?) - AND DATE_FORMAT(st.date, '%H:%i') >= '00:00' AND DATE_FORMAT(st.date, '%H:%i') <= '23:59'
  • You only need 10 results, but the database will have to run the "names" subquery for each of the results before the LIMIT (which might be a lot?). Therefore, I would recommend to extract the subquery from the SELECT clause to a temporary table, index it and join to it (see fixed query below).

To optimize the query, let's begin with adding these indexes:

ALTER TABLE `classes` ADD INDEX `classes_index_1` (`id`, `name`);
ALTER TABLE `students` ADD INDEX `students_index_1` (`active`, `user_id`, `class`, `name`, `date`);
ALTER TABLE `users` ADD INDEX `users_index_1` (`user_id`, `full_name`);

Now create the temporary table (originally this was a subquery in the SELECT clause) and index it:

-- Transformed subquery to a temp table to improve performance
CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT
        count(*) AS names,
        name 
    FROM
        students AS inner_st 
    WHERE
        1 = 1 
    GROUP BY
        name 
    ORDER BY
        NULL

-- This index is required for optimal temp tables performance
ALTER TABLE
  `temp1`
ADD
  INDEX `temp1_index_1` (`name`, `names`);

And the modified query:

SELECT
        SQL_CALC_FOUND_ROWS st.id,
        st.date,
        st.image,
        st.user_id,
        st.status,
        ck.name AS class_name,
        users.full_name,
        temp1.names 
    FROM
        students AS st 
    LEFT JOIN
        users 
            ON st.user_id = users.user_id 
    LEFT JOIN
        classes AS ck 
            ON st.class = ck.id 
    LEFT JOIN
        temp1 
            ON st.name = temp1.name 
    WHERE
        st.date BETWEEN '2018-01-17' AND DATE_ADD('2018-01-17', INTERVAL 1 DAY) 
        AND st.active = 1 
    ORDER BY
        st.date DESC LIMIT 0,
        10

Upvotes: 1

Related Questions