CODEWITHSUNDEEP
mysqlselect
Wikunia
Wikunia

Reputation: 1592

Select all items with same categories

I have a table CategoryLinks with two columns (article_id,category_id) both integer indexed. Together it's my unique index.

I want to have all article_ids which have the categorie_ids 1,2,3 or 4,5

I tried it with in(1,2,3,4,5) and using php afterwards but it's slow and should be possible with MySql I think.

Btw. CategoryLinks has over 500,000 rows

Thanks for any tips!

Show Create table CategoryLinks

CREATE TABLE `CategoryLinks` (
  `article_id` int(10) NOT NULL,
  `category_id` int(7) NOT NULL,
  UNIQUE KEY `Unique` (`article_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `article_id` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Upvotes: 0

Views: 66

Answers (2)

Strawberry
Strawberry

Reputation: 33935

...or if less code is your thing...

CREATE TABLE `CategoryLinks` (
  `article_id` int NOT NULL,
  `category_id` int NOT NULL,
  PRIMARY KEY (`article_id`,`category_id`)
);

SELECT COUNT(*) FROM categorylinks;
+----------+
| COUNT(*) |
+----------+
|   107943 |
+----------+

SELECT x.article_id
   FROM categorylinks x
  GROUP
     BY article_id
 HAVING SUM(category_id IN (1,2,3)) = 3
     OR SUM(category_id IN (4,5)) =2;
+------------+
| article_id |
+------------+
|          1 |
...
|      19860 |
+------------+
9573 rows in set (0.06 sec)

And the EXPLAIN...

           id: 1
  select_type: SIMPLE
        table: x
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 28719
        Extra: Using index

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Something like 

Select L1.article_id, "OneTwoThree" as CategoryGroup 
From CategoryLinks L1 On L1.Category_id = 1
inner join CategoryLinks L2 On L2.Category_id = 2 and L2.article_id = L1.article_id
inner join CategoryLinks L3 On L3.Catgeory_id = 3 and L3.article_id = l1.article_id
union 
Select L4.article_id, "FourFive" as CategoryGroup 
From CategoryLinks L4 On L4.Category_id = 4
inner join CategoryLinks L5 On L5.Category_id = 5 and L5.article_id = L4.article_id

would be one way to do it.

Upvotes: 0

Related Questions