Mysql – ORDERBY timestamp performance issue

geometryMySQLmysql-5.5order-byperformance

I have some queries dealing with coordinates stored in POINT.
Everything is ok without order (about 0.6 ms to query a 5 millions posts and 1 million users database, for testing) but go to 13s when add ORDER BY created_at DESC.)

All fields in WHERE clauses have index.
Created_at index is DESC sort.

Deal is to recover Posts submitted by Users in Geographical Sphere.

SET @radius = 30;

SET @center = GeomFromText('POINT(12.005 -1.001)');
SET @r = @radius/69.1;
SET @bbox = CONCAT('POLYGON((', 
  X(@center) - @r, ' ', Y(@center) - @r, ',', 
  X(@center) + @r, ' ', Y(@center) - @r, ',', 
  X(@center) + @r, ' ', Y(@center) + @r, ',', 
  X(@center) - @r, ' ', Y(@center) + @r, ',', 
  X(@center) - @r, ' ', Y(@center) - @r, '))' 
);

SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id 
WHERE posts.user_id IN (
    SELECT user_id  FROM users_contact WHERE
    Intersects( coordinates, GeomFromText(@bbox) ) 
    AND SQRT(POW( ABS( X(coordinates) - X(@center)), 2) + POW( ABS(Y(coordinates) - Y(@center)), 2 )) < @r )
    AND users.user_status=1 AND posts.status=1 AND posts.created_at <= '2016-10-24 10:30:53' AND posts.context = '1'
# ORDER BY posts.created_at DESC 
LIMIT 30;

Tried INNER JOIN instead of IN Clause, but result is the same.
Putting Coordinates in users table instead of users_contact is the same.

Thanks, MySQL 5.5

Best Answer

This is normal situation

  • when You run query without ORDER BY - LIMIT 30; work perfect - it stop query after first 30 records, any 30 records
  • When You add ORDER BY - MySQL first send all records to temporary table (if dataset big), than sort it, and then return to You 30 correct records

So on huge dataset - it work as expected. Solution really only one - analyse query and reduce number of total records returned by query, how - depend from business logic

For example - posts.created_at <= '2016-10-24 10:30:53' (it mean yesterday) return all records from 5 000 000, but if add posts.created_at >= '2016-09-24 10:30:53' it return only month of data and etc - any legal ways for reduce number of records.

Second recommendation depend from Your table structure, some time replace SELECT * to the SELECT "WHAT REALLY NEED" could dramatically increase speed - for example one of column BLOB or TEXT, so exclude this column from SELECT - reduce size of data before sort for many. many times. Even if this BLOB column need for You - You can request it by ID (single record) by second query from the page

Related Question