how to speed up query when using count and group by
<p>I have two tables named <strong>seller</strong> and <strong>item</strong>. They are connected through a third table (<strong>seller_item</strong>) using a "n" to "m" foreign key relation. </p>

<p>Now I a try to answer the requirement: "I as a seller want a list of my competitors with a count of items I am selling and they are selling as well".
So a list of all sellers with the count of overlapping items in relation to one specific seller. 
Also I want this to be sorted by count and limited.
But the query is using temp table and filesort which is very slow. 
Explain says: </p>

<p><strong>Using where; Using index; Using temporary; Using filesort</strong></p>

<p>How can I speed this up ?</p>

<p>Here is the query:</p>

<pre><code>SELECT
          COUNT(*) AS itemCount,
          s.sellerName
        FROM
          seller s,
          seller_item si
        WHERE
          si.itemId IN 
           (SELECT itemId FROM seller_item WHERE sellerId = 4711)
        AND
          si.sellerId=s.id
        GROUP BY
          sellerName
        ORDER BY
          itemCount DESC
        LIMIT 50;
</code></pre>

<p>the table defs:</p>

<pre><code>CREATE TABLE `seller` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sellerName` varchar(50) NOT NULL
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`sellerName`),
) ENGINE=InnoDB 

contains about 200.000 rows
</code></pre>

<p>--</p>

<pre><code>CREATE TABLE `item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`itemName` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`itemName`),
) ENGINE=InnoDB

contains about 100.000.000 rows
</code></pre>

<p>-- </p>

<pre><code>CREATE TABLE `seller_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sellerId` bigint(20) unsigned NOT NULL,
`itemId` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sellerId` (`sellerId`,`itemId`),
KEY `item_id` (`itemId`),
CONSTRAINT `fk_1` FOREIGN KEY (`sellerId`) REFERENCES `seller` (`id`)            ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `fk_2` FOREIGN KEY (`itemId`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB

contains about 170.000.000 rows
</code></pre>

<p>Database is Mysql Percona 5.6</p>

<p>Output of EXPLAIN:</p>

<pre><code>+----+-------------+-------------+--------+----------------------+-----    ---------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys        | key                | key_len | ref                 | rows | Extra                                           |
+----+-------------+-------------+--------+----------------------+--------------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s           | index  | PRIMARY,unique_index | unique_index | 152     | NULL                |    1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | si          | ref    | sellerId,item_id     | sellerId     | 8       | tmp.s.id            |    1 | Using index                                  |
|  1 | SIMPLE      | seller_item | eq_ref | sellerId,item_id     |  sellerId     | 16      | const,tmp.si.itemId |    1 | Using where; Using  index                     |
+----+-------------+-------------+--------+----------------------+--------------+---------+---------------------+------+----------------------------------------------+
</code></pre>