Geek Answers Handbook

MySQL Query Order by Cluase takes too much time

I have a request that only accepts .001 seconds in no order. But when I add an order on demand, it takes about .736 seconds. it's too much time. I indexed this column, but it is not working properly.

Here is the request:

SELECT DISTINCT
n0_.id AS id0, n0_.published_at AS published_at1
FROM
news n0_
    INNER JOIN
news_translations n1_ ON n0_.id = n1_.translatable_id
WHERE
((n0_.unpublished_at IS NOT NULL
    AND (CURRENT_TIMESTAMP >= n0_.published_at
    AND CURRENT_TIMESTAMP < n0_.unpublished_at))
    OR (CURRENT_TIMESTAMP >= n0_.published_at
    AND n0_.unpublished_at IS NULL))

    AND  
        (n0_.status = 1
     AND n0_.content_type_id = 1)
      AND  n1_.locale = 'zh_CN'
     and n0_.id NOT IN (139476 , 226, 225, 224, 223, 218, 213, 69, 65, 62)   
GROUP BY n0_.id
#ORDER BY n0_.published_at DESC
LIMIT 10 OFFSET 0;

Here is the output of SHOW KEYS:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type
news    0   PRIMARY 1   id  A   139656  NULL    NULL        BTREE
news    0   UNIQ_1DD399501204232D   1   comment_box_id  A   139656  NULL    NULL    YES BTREE
news    1   IDX_1DD39950510C33D5    1   approval_type_id    A   4   NULL    NULL    YES BTREE
news    1   IDX_1DD399501A445520    1   content_type_id A   36  NULL    NULL    YES BTREE
news    1   IDX_1DD39950A76ED395    1   user_id A   4   NULL    NULL    YES BTREE
news    1   IDX_1DD3995042A9A1DE    1   smiley_group_id A   4   NULL    NULL    YES BTREE
news    1   IDX_1DD39950B3FE509D    1   survey_id   A   4   NULL    NULL    YES BTREE
news    1   IDX_1DD3995029C1004E    1   video_id    A   4   NULL    NULL    YES BTREE
news    1   IDX_1DD399501CDF82CA    1   cover_image A   4   NULL    NULL    YES BTREE
news    1   IDX_1DD399504E7AF8F     1   gallery_id  A   4   NULL    NULL    YES BTREE
news    1   IDX_1DD399506048130B    1   home_position_id    A   4   NULL    NULL    YES BTREE
news    1   news_published_at_index 1   published_at    A   139656  NULL    NULL        BTREE
+4
source share
1 answer

Sorting is an expensive operation, so it takes time to arrange many rows. One approach is to shorten the results before ordering, but this is not always possible.

, . , id (, ). , , , , . , , . , NOW() , , .

+1

More articles:


All Articles