Optimize 5 SQL table queries (store => items => words)

Question

Tables

saves (100,000 lines) : id (pk), name, lat, lng, ...

store_items (9,000,000 rows) : store_id (fk), item_id (fk)

elements (200,000 rows) : id (pk), name, ...

item_words (1,000,000 lines) : item_id (fk), word_id (fk)

words (50,000 lines) : id (pk), word VARCHAR (255)

Note: all identifiers are integers.

========

Indices

CREATE UNIQUE INDEX storeitems_storeid_itemid_i ON store_items (store_id, item_id);

CREATE A UNIQUE INDEX itemwords_wordid_itemid_i ON item_words (word_id, item_id);

CREATE A UNIQUE INDEX words_word_i ON words (word);

. (storeitems_storeid_itemid_i itemwords_wordid_itemid_i), : http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/

QUERY

select s.name, s.lat, s.lng, i.name
from words w, item_words iw, items i, store_items si, stores s
where iw.word_id=w.id
and i.id=iw.item_id
and si.item_id=i.id
and s.id=si.store_id
and w.word='MILK';

: 20-120 ( )!!!

explain $QUERY$
+----+-------------+-------+--------+-------------------------------------------------------+-----------------------------+---------+-----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                         | key                         | key_len | ref                         | rows | Extra       |
+----+-------------+-------+--------+-------------------------------------------------------+-----------------------------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | w     | const  | PRIMARY,words_word_i                                  | words_word_i                | 257     | const                       |    1 | Using index |
|  1 | SIMPLE      | iw    | ref    | itemwords_wordid_itemid_i,itemwords_itemid_fk         | itemwords_wordid_itemid_i   | 4       | const                       |    1 | Using index |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY                                               | PRIMARY                     | 4       | iw.item_id                  |    1 |             |
|  1 | SIMPLE      | si    | ref    | storeitems_storeid_itemid_i,storeitems_itemid_fk      | storeitems_itemid_fk        | 4       | iw.item_id                  |   16 | Using index |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                                               | PRIMARY                     | 4       | si.store_id                 |    1 |             |

, 5 !!! ???

==============

, .

1

select * from words where word='MILK';

Elapsed time: 0.4 sec

2

select count(*)
from words w, item_words iw
where iw.word_id=w.id
and w.word='MILK';

Elapsed time: 0.5-2 sec (depending on word)

3

select count(*)
from words w, item_words iw, items i
where iw.word_id=w.id
and i.id=iw.item_id
and w.word='MILK';

Elapsed time: 0.5-2 sec (depending on word)

4

select count(*)
from words w, item_words iw, items i, store_items si
where iw.word_id=w.id
and i.id=iw.item_id
and si.item_id=i.id
and w.word='MILK';

Elapsed time: 20-120 sec (depending on word)

, / . . Google -, !

Answer 1

a) , FTS mysql → FTS, , lucene.

b) , 9M

c) (, ) :

SELECT
    s.name, s.lat, s.lng, i.name
FROM
    (SELECT * FROM words WHERE word='MILK') w
INNER JOIN
    item_words iw
ON
    iw.word_id=w.id
INNER JOIN
    items i
ON
    i.id=iw.item_id
INNER JOIN
    store_items si
ON
    si.item_id=i.id
INNER JOIN
    stores s
ON
    s.id=si.store_id;

, , , , , ( , ) .

d) Google mysql FTS

Answer 2

- - - 1 item_words table - . ( , , , , ). - - ( MySQL, , , ).

Answer 3

, store_id, item_id. store_id , storeitems_storeid_itemid_i, 100 000 , . store_items, item_id:

CREATE UNIQUE INDEX storeitems_item_store ON store_items(item_id, store_id);

, , where , , - :

select s.name, s.lat, s.lng, i.name
from words w LEFT JOIN item_words iw ON w.id=iw.word_id
LEFT JOIN items i ON i.id=iw.item_id
LEFT JOIN store_items si ON si.item_id=i.id
LEFT JOIN stores s ON s.id=si.store_id
where w.word='MILK';

Answer 4

, . , . , , , .

, - , SQL. , 2 4 . , , , .

, , . , .

Answer 5

.

select s.name, s.lat, s.lng, i.name  
from words w LEFT JOIN item_words iw ON w.id=iw.word_id AND w.word='MILK'  
LEFT JOIN items i ON i.id=iw.item_id  
LEFT JOIN store_items si ON si.item_id=i.id  
LEFT JOIN stores s ON s.id=si.store_id  

(w.id, w.word)

Answer 6

Have you tried analyzing tables? this will help the optimizer choose the best execution plan possible.

eg:

ANALYZE TABLE words
ANALYZE TABLE item_words
ANALYZE TABLE items
ANALYZE TABLE store_items
ANALYZE TABLE stores

see http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html