Group by without temporary table and filesort

Question

My aim is to make the following query faster.

SELECT SQL_NO_CACHE Links.id,Links.link,sum(ID_Links_Words.count) AS wordCount 
 FROM Links LEFT JOIN ID_Links_Words ON Links.id = ID_Links_Words.link 
 WHERE ID_Links_Words.word IN ('1025','1056','2353') GROUP BY Links.id

My table Links has the columns

id,link,title,description and timestamp

and the keys id (Primary),link (index), timestamp (index), (link,title,description) (fulltext)

The table ID_Links_Words has the columns

link, word, count

and the keys (link,word) (UNIQUE), link (index), word (index)

 |id | select_type | table         | type | possible_keys     | key | key_len | ref                | rows | Extra 
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|
 | 1     SIMPLE     ID_Links_Words   range link,link_2,word    word     4       NULL                 6        Using where; Using  temporary; Using filesort|
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|
 | 1     SIMPLE     Links           eq_ref   PRIMARY           PRIMARY   4     ID_Links_Words.link   1
 |---------------------------------------------------------------------------------------------------------------------------------------------------------|

Sorry for this bad explain table :/ Hope you can read it. I would like to get rid of the using temporary and I'm not sure if it's possible. Are there any options to improve this sql statement? I might have to deal with about 100,000 words and 1,000 links.

Answer 1

Is ID_Links_Words a many-to-many table for connecting Words and Links? Then this is the optimal schema:

CREATE TABLE ID_Links_Words(
    word ... NOT NULL,
    link ... NOT NULL,
    count ... NOT NULL,
    PRIMARY KEY(word, link), -- for going one direction
    INDEX(link, word)        -- and the other
) ENGINE = InnoDB;           -- Important because of the clustering of the PK.

The two indexes can be swapped, depending on the various queries you do; this arrangement -- with word first in the PRIMARY KEY -- is best for this SELECT.

Remove LEFT unless there is a compelling reason for it.

What percentage of the table has those 3 'words'? If it is a non-trivial percentage, you are stuck with a table scan.

Check the settings of innodb_buffer_pool_size and key_buffer_size.

What version of MySQL are you using? There were improvements in the handling of IN(...) in 5.6, perhaps before. If you are in an older version, recommend upgrading.

If the my other hints don't do enough, try this:

(   SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '1025'
        GROUP BY  W.link 
)
UNION  ALL (
    SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '1056'
        GROUP BY  W.link 
           )
UNION  ALL (
    SELECT  SQL_NO_CACHE L.id, L.link, sum(W.count) AS wordCount
        FROM  Links AS L
        LEFT JOIN  ID_Links_Words AS W ON L.id = W.link
        WHERE  W.word = '2353'
        GROUP BY  W.link
           );

And, assuming that the LEFT is not necessary, test this

SELECT W.link as id, ( SELECT link FROM Links WHERE id = W.link ) AS link, wordCount FROM
( SELECT SUM(count) AS wordCount FROM ID_Links_Words WHERE word IN ('1025','1056','2353') GROUP BY link ) AS W ;

This, if I wrote it right, has the following potential benefits:

• The GROUP BY is done without being bothered by the other table.
• The other table is done after shrinking the number of rows by the GROUP BY.

Again, the IN could be turned into a UNION.

Edit

There is a world of difference between INDEX(word, link) and INDEX(link, word). INDEXes are ordered. If you have a word and need to find the links associated with it, the former is much better. If you have a link and want to find the words the latter is much better. Imagine looking in a list of names ordered by (last_name, first_name), and looking for all the last names, given a first name.

Index cookbook