Re: Slow query with order by and limit

[Rick James]

"hardware ... with 256Mb cache" -- this is yet another 'cache'. It speeds up the CPU when it is reading from memory. It has nothing (directly) to do with MySQL.

"If ordering is done by using an index, this is very fast." -- true, only in cases simpler than what you have. 172837 says that it could not do it the "very fast" way. If Rows examined were 3, then I would believe that "ORDER BY ... LIMIT 3" used that optimization.

"What can I do to eliminate it from slow_query?" All 14 messages in this thread have been addressing this question. It's looking like there is not an answer. At least, not with the current schema and select ideas.

If you were to "denormalize", that is, put all the info into a single table, and if several more things, then you might get Rows examined down to a "very fast" 3. This might get all the queries like this one to run in under 0.1 sec.

You would need _one_ table (maybe in addition to the existing tables) with fields
tid, vid, created

SELECT vid AS vid
    FROM  denorm
    WHERE  tid = 10051
    ORDER BY  created DESC
    LIMIT  0, 3;

and indexed by, specifically,
(tid, created, vid)

You can test it out (without modifying your code to maintain it):

CREATE TABLE denorm (
    tid ...,
    created ...,
    vid ...
    PRIMARY KEY(tid, created, vid);
INSERT INTO denorm
    SELECT  tn.tid,
            n.created
            n.vid
        FROM  node n
        INNER JOIN  term_node tn  ON n.vid = tn.vid;
# Then benchmark
SELECT  SQL_NO_CACHE  vid
    FROM  denrom
    WHERE  tid = 12345
    ORDER BY  created DESC
    LIMIT  3