0

I have Visitor Management System - time spent at work by specyfic user. When I add ORDER BY a added_on it return the results after at least 18 seconds (even though there is pagination (32 per page)) but if I do the same query without ORDER BY then it return the results for under 0,5 seconds.

I have a MySQL table like this:

+-----+----------+------------+--------------+-------------+
| id  |  user_id |  added_on  | status_enter | status_exit |
+-----+----------+------------+--------------+-------------+

CREATE TABLE `visitors` (
    `id` int(11) NOT NULL auto_increment,
    `user_id` int(11) NOT NULL,
    `added_on` datetime NOT NULL,
    `status_enter` tinyint(1) DEFAULT NULL,
    `status_exit` tinyint(1) DEFAULT NULL,
     PRIMARY KEY (id)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sql

    $sql = "select
      tc.id,
      tc.user_id,
      tc.added_on,
       username,
    ( select min( tc2.added_on )
           from visitors tc2
           where tc.User_id = tc2.User_id
    and tc.id < tc2.id
    and tc2.status_enter = 0
    and tc2.status_exit = 1 ) EndTime,
    ( select min( tc2.id )
           from visitors tc2
           where tc.User_id = tc2.User_id
    and tc.id < tc2.id
    and tc2.status_enter = 0
    and tc2.status_exit = 1 ) EndTimeID
   from
      visitors tc
        INNER JOIN users ON tc.user_id = users.enter_code
   where
      tc.status_enter = 1 ";

        //$sql .= "ORDER BY added_on DESC ";
        $sql .= "LIMIT {$per_page} ";
        $sql .= "OFFSET {$pagination->offset()}";

There are currently 30,000 records in the database.

What can I do to preform this ORDER BY faster?

2
  • Is it only the primary key index that exists? You’ll want one to support your order by clause (possibly with the filter column if that helps to reduce the data set substantially). You’ll also want an index to support the scalar subqueries Jan 4 at 12:37
  • 1
    Keyset Pagination is probably a good solution, see use-the-index-luke.com/no-offset Jan 4 at 14:55
1

min( tc2.added_on ) and min( tc2.id ) will not necessary align with each other. Sounds like you need a "groupwise-max".

INDEX(User_id, added_on)
INDEX(status_enter, added_on)

Using OFFSET for pagination is problematic, especially with poor indexes.

2
  • Hello, I added 'index' and the same, I removed INNER JOIN users ON tc.user_id = users.enter_code and works fine, but I don't understand it at all. Jan 14 at 10:46
  • @Mantykora7 - Maybe these will help explain indexing and pagination
    – Rick James
    Jan 14 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.