ORDER BY Making Query Very Slow


#1
{
  "name": "name",
  "email": "name@yahoo.in",
  "password": "password",
  "new_password_key": null,
  "password_requested_timestamp": null,
  "last_visited": "2017-11-24 19:47:22",
  "registered_timestamp": "2017-11-24 19:47:22",
  "user_name": "test123",
  "user_id": "fdhgdhfjghdjkghgj45645",
  "old_user_id": 853151772,
  "mobile_verify": 0
}

Query - SELECT * FROM users WHERE registered_timestamp BETWEEN "2000-01-01" AND "2017-05-18 24:00:00" ORDER BY registered_timestamp DESC LIMIT 10 OFFSET 0;

Index - CREATE INDEX registered_timestamp_users ON users(registered_timestamp DESC);

  • Issue - Above Query taking 5000ms when querying in 2lac data

#2

The index is right. What is CB version and post the EXPLAIN

Also try specifying hint

 SELECT * FROM users USE INDEX (registered_timestamp_users) 
WHERE registered_timestamp BETWEEN "2000-01-01" AND "2017-05-18 24:00:00" 
ORDER BY registered_timestamp DESC LIMIT 10 OFFSET 0;

#3

thanx @vsr1 , now it’s working.