title: Get number of words from two tables
body: <p>I have two tables: <code>z_words</code>, and <code>z_words_products</code>. You can see bellow the structure of these tables.</p>
  
  <p>In the <code>z_words_products</code> table could be the same values for: </p>
  
  <p><code>wordId</code>,<code>smaId</code>,<code>productId</code>,<code>siteId</code> and the <code>type</code> value is different. </p>
  
  <p>What I want to achive, to get 1 number, a count. I want to know, how many words in my table, but one word counts one in a product, even, if it occures twice in one product.</p>
  
  <p>For example:</p>
  
  <pre><code>SELECT `zw`.`word`, `zwp`.`smaId`, COUNT(*) AS `cnt` 
  FROM `z_words` `zw`
  INNER JOIN `z_words_products` `zwp` ON `zwp`.`wordId` = `zw`.`id`
  WHERE `zwp`.`siteId` = 0 
  AND `zwp`.`smaId` = 1 
  GROUP BY `zw`.`word`, `zwp`.`smaId`  
  </code></pre>
  
  <p><strong>Output</strong></p>
  
  <pre><code>+--------------+-------+-----+
  |     word     | smaId | cnt |
  +--------------+-------+-----+
  | 8100         |     1 |   1 |
  | 8102wlci     |     1 |   1 |
  | 8104wlmi     |     1 |   1 |
  | Acer         |     1 |   2 |
  | unknown      |     1 |   1 |
  +--------------+-------+-----+
  </code></pre>
  
  <p>As you see, Acer occures twince, because it is in with type <code>A</code> and type <code>B</code>, but I at the final result it should count as 1.</p>
  
  <p>So my final query result should be 5, not 6. Of course, there are thousends of products in my table.</p>
  
  <p>I've tried this:</p>
  
  <pre><code>SELECT COUNT(cnt) FROM (
     SELECT `zw`.`id` AS `cnt` 
     FROM `z_words` `zw`
     INNER JOIN `z_words_products` `zwp` ON `zwp`.`wordId` = `zw`.`id`
     WHERE `zwp`.`siteId` = 0 
     AND `zwp`.`smaId` = 1
     GROUP BY `zw`.`word`, `zwp`.`smaId`) 
   AS xxx
  </code></pre>
  
  <p>It works, gives me back the right result. My problem is, when I run it on all the products it tooks 6-7 minuites.</p>
  
  <p>Is there another way to get the results faster?</p>
  
  <p>In the <code>z_words_products</code> are 6 147 059 records, in the <code>z_words</code> table are 367 771</p>
  
  <p>Eplain of the query:</p>
  
  <pre><code>+----+-------------+------------+--------+-----------------------------------------------+--------------+---------+------------------------+------+----------------------------------------------+
  | id | select_type |   table    |  type  |                 possible_keys                 |     key      | key_len |          ref           | rows |                    Extra                     |
  +----+-------------+------------+--------+-----------------------------------------------+--------------+---------+------------------------+------+----------------------------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt; | ALL    | \N                                            | \N           | \N      | \N                     |   14 |                                              |
  |  2 | DERIVED     | zwp        | ref    | PRIMARY,siteId_smaId,wordId,wordId_2,wordId_3 | siteId_smaId | 8       | const,const            |   14 | Using index; Using temporary; Using filesort |
  |  2 | DERIVED     | zw         | eq_ref | PRIMARY                                       | PRIMARY      | 4       | partcollect.zwp.wordId |    1 |                                              |
  +----+-------------+------------+--------+-----------------------------------------------+--------------+---------+------------------------+------+----------------------------------------------+
  </code></pre>
  
  <p><strong>The tables</strong></p>
  
  <pre><code>CREATE TABLE `z_words` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `word` varchar(255) DEFAULT NULL,
    `translated` varchar(255) DEFAULT NULL,
    `transTry` tinyint(1) DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `word` (`word`)
  ) ENGINE=InnoDB AUTO_INCREMENT=526303 DEFAULT CHARSET=utf8
  
  CREATE TABLE `z_words_products` (
    `wordId` int(11) NOT NULL,
    `smaId` int(11) NOT NULL,
    `productId` int(11) NOT NULL,
    `type` enum('word','compatible','partNumber','tag','model') NOT NULL,
    `siteId` int(11) NOT NULL DEFAULT '0',
    `order` int(11) DEFAULT NULL,
    PRIMARY KEY (`wordId`,`smaId`,`productId`,`type`,`siteId`),
    KEY `siteId_smaId` (`siteId`,`smaId`),
    KEY `wordId` (`wordId`,`productId`),
    KEY `wordId_2` (`wordId`,`order`),
    KEY `wordId_3` (`wordId`),
    KEY `wordId_4` (`productId`,`order`),
    CONSTRAINT `z_words_products_ibfk_1` FOREIGN KEY (`wordId`) REFERENCES `z_words` (`id`) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>