MySQL: объединение одной и той же таблицы с миллионами строк

#mysql #join

#mysql #Присоединиться

Вопрос:

У меня есть таблица с миллионами строк (SF_COLLECTIONS)

 ID MEMBERID COLLECTIONID CARDID STATE (D / M)
1  1        1            1      D
2  1        1            2      D
3  2        1            1      M
4  2        1            2      M
5  2        1            3      D
6  1        1            3      M

и я должен скрестить те, у которых есть MEMBERID = 1 и STATE = D, с теми, у которых есть MEMBERID = 2 и STATE = M, и наоборот

Это мой запрос

 SELECT 1
    FROM sf_collections AS rac
    INNER JOIN sf_collections AS myrac 
        ON 
        (myrac.cardid = rac.cardid AND 
            (
                (myrac.state = "M" AND rac.state = "D") OR 
                (myrac.state = "D" AND rac.state = "M")
            )
        ) 
    WHERE
    rac.memberid = 1 AND myrac.memberid = 2
    GROUP BY rac.memberid

(время отклика около 4 секунд)

Является ли это правильным подходом или есть лучший способ повысить производительность?

Образец набора данных:

 CREATE TABLE `sf_collections` (
 `id` int(11) NOT NULL auto_increment,
 `memberid` int(11) NOT NULL,
 `collectionid` int(11) NOT NULL,
 `cardid` int(11) NOT NULL,
 `state` varchar(1) NOT NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `sf_collections_pkey` (`memberid`,`collectionid`,`cardid`,`state`),
 KEY `collectionid` (`collectionid`),
 KEY `memberid` (`memberid`),
 KEY `cardid` (`cardid`),
 KEY `state` (`state`)
) ENGINE=MyISAM AUTO_INCREMENT=22627806 DEFAULT CHARSET=latin1

INSERT INTO sf_collections (memberid,collectionid,cardid,state) VALUES
(1,1,1,'D'),
(1,1,2,'D'),
(1,1,3,'M'),
(2,1,1,'M'),
(2,1,2,'M'),
(2,1,3,'D');

SELECT 1
    FROM sf_collections AS rac
    INNER JOIN sf_collections AS myrac 
        ON 
        (myrac.cardid = rac.cardid AND 
            (
                (myrac.state = "M" AND rac.state = "D") OR 
                (myrac.state = "D" AND rac.state = "M")
            )
        ) 
    WHERE
    rac.memberid = 1 AND myrac.memberid = 2
    GROUP BY rac.memberid

и db-скрипка

Спасибо

РЕДАКТИРОВАТЬ: MySQL 5.0 (очень старый и не может его обновить)

1. попробуйте удалить целое OR и запустить запрос.. сколько времени это займет?

2. При отсутствии каких-либо агрегирующих функций предложение GROUP BY никогда не подходит

3. Ну, не зная, что вам на самом деле нужно, трудно посоветовать, как туда добраться.

4. Обратите внимание, что KEY memberid... это излишне, и почему вы используете MyISAM, а не InnoDB?

5. Нет. Индекс работает в том порядке, в котором он определен. Если первый столбец в индексе не используется запросом, MySQL не может просто пропустить его и перейти к следующему столбцу в индексе. Имеет ли это смысл? — Это как индекс в конце книги. Первая часть индекса — это поисковый запрос, расположенный в алфавитном порядке, вторая часть индекса — номер страницы. Хотя полезно и практично искать поисковый запрос, чтобы найти номер страницы, непрактично искать номер страницы, чтобы найти поисковый запрос.

Ответ №1:

Замените INDEX(cardid) на INDEX(cardid, state)

Если это практично, не проверяйте оба D-> M и M-> D; просто сделайте одно направление. Это сократит вдвое усилия.

Избегайте SELECT ... GROUP BY ... переключения на EXISTS ( SELECT 1 ... ) . Если есть несколько совпадающих строк, это ускорит процесс. Если вы в конечном итоге будете «перечислять совпадения», мы можем также посмотреть, чего вы хотите, а не придираться к плохому использованию GROUP BY . Будете ли вы использовать a GROUP_CONCAT ?

Перейдите с MyISAM на InnoDB. Даже в версии 5.0 этот запрос, возможно, был изначально быстрее.

DROP INDEX(memberid) поскольку уникальный индекс обрабатывает такие.

Вам id что-нибудь нужно? Если нет, избавьтесь от него и сделайте UNIQUE индекс из 4 столбцов PRIMARY KEY равным .

Если state это просто флаг (0/1), INDEX(state) он, вероятно, никогда не будет использоваться; отбросьте его.