Geek asks and answers

Can it run faster with a lot of data [MySQL]

is there any way to optimize the following query:

EXPLAIN EXTENDED SELECT keyword_id, ck.keyword, COUNT( article_id ) AS cnt
FROM career_article_keyword
LEFT JOIN career_keywords ck
USING ( keyword_id ) 
WHERE keyword_id
IN (

SELECT keyword_id
FROM career_article_keyword
LEFT JOIN career_keywords ck
USING ( keyword_id ) 
WHERE article_id
IN (

SELECT article_id
FROM career_article_keyword
WHERE keyword_id =9
)
AND keyword_id <>9
)
GROUP BY keyword_id
ORDER BY cnt DESC

The main task here, if I have a specific keyword_id (CURRENT_KID), I need to find all the keywords that ever belonged to any article along with CURRENT_KID, and the sorting result is based on the amount of use of these keywords

tables defined as:

mysql> show create table career_article_keyword;
+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                  | Create Table                                                                                                                                                                                                                                                                                                                                               |
+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| career_article_keyword | CREATE TABLE `career_article_keyword` (
  `article_id` int(11) unsigned NOT NULL,
  `keyword_id` int(11) NOT NULL,
  UNIQUE KEY `article_id` (`article_id`,`keyword_id`),
  CONSTRAINT `career_article_keyword_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `career` (`menu_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table career_keywords;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                         |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| career_keywords | CREATE TABLE `career_keywords` (
  `keyword_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `keyword` varchar(250) NOT NULL,
  PRIMARY KEY (`keyword_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

the conclusion of "explanation" scares me

http://o7.no/J6ThIs

on big data, this query can kill everything :) Can I do it faster?

thank.

0
source share
1 answer

EXPLAIN, , . ( - - ), JOIN .

, , . , , , , , , .

:

  • " , " ( ):

    SELECT ca2.article_id
FROM
       career_article_keyword AS ca2
WHERE
      ca2.keyword_id = 9;

  • " , []"

    SELECT ca1.keyword_id
FROM
       career_article_keyword AS ca1
  JOIN career_article_keyword AS ca2 ON (ca2.article_id = ca1.article_id)
WHERE
      ca1.keyword_id <> 9
  AND ca2.keyword_id =  9
GROUP BY ca1.keyword_id;

  • "[] , :

    SELECT ca1.keyword_id, COUNT(DISTINCT ca0.article_id) AS cnt
FROM
       career_article_keyword AS ca0
  JOIN career_article_keyword AS ca1 USING (keyword_id)
  JOIN career_article_keyword AS ca2 ON (ca2.article_id = ca1.article_id)
WHERE
      ca1.keyword_id <> 9
  AND ca2.keyword_id =  9
GROUP BY ca1.keyword_id
ORDER BY cnt DESC;

  • , career_keyword:

    SELECT ck.keyword_id, ck.keyword, COUNT(DISTINCT ca0.article_id) AS cnt
FROM
       career_keywords        AS ck 
  JOIN career_article_keyword AS ca0 USING (keyword_id)
  JOIN career_article_keyword AS ca1 USING (keyword_id)
  JOIN career_article_keyword AS ca2 ON (ca2.article_id = ca1.article_id)
WHERE
      ca1.keyword_id <> 9
  AND ca2.keyword_id =  9
GROUP BY ck.keyword_id -- equal to ca1.keyword_id due to join conditions
ORDER BY cnt DESC;

, career_keywords , ; - (.. ), .

, , :

  • career_keywords.keyword_id ck JOIN ca0

    PRIMARY KEY (`keyword_id`), , .

  • career_article_keyword.article_id ca1 JOIN ca2

    UNIQUE KEY `article_id` (`article_id`,`keyword_id`), article_id , , .

  • career_article_keyword.keyword_id ck JOIN ca0 ca0 JOIN ca1

    , : , , , article_id keyword_id - MySQL keyword_id article_id. , keyword_id .

    ( , .)

+2

More articles:


All Articles