Having trouble optimizing the MySQL query with GROUP BY & hellip; HAVING

I'm trying to optimize quickly optimize the search functionality of some outdated forum software written in PHP. I've got my work down to a query that looks like this:

SELECT thread.threadid
FROM thread AS thread
INNER JOIN word AS word ON (word.title LIKE 'word1' OR word.title LIKE 'word2')
INNER JOIN postindex AS postindex ON (postindex.wordid = word.wordid)
INNER JOIN post AS postquery ON (postquery.postid = postindex.postid)
WHERE thread.threadid = postquery.threadid
GROUP BY thread.threadid
HAVING COUNT(DISTINCT word.wordid) = 2
LIMIT 25;

word1 and word2 are examples; there could be any number of words. The number at the very end of the query is the total number of words. The idea is that a thread most contain all words in the search query, spread out over any number of posts.

This query often exceeds 60 seconds with only two words, and times out. I'm stumped; I can't figure out how to further optimize this horrid search engine.

As far as I can tell, everything is indexed properly, and I've run ANALYZE recently. Most of the database is running on InnoDB. Here's the output of EXPLAIN:

+----+-------------+-----------+--------+----------------------------------------------------------------------------------------+---------+---------+------------------------------+------+-----------------------------------------------------------+
| id | select_type | table     | type   | possible_keys                                                                          | key     | key_len | ref                          | rows | Extra                                                     |
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------+---------+---------+------------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | word      | range  | PRIMARY,title                                                                          | title   | 150     | NULL                         |    2 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | postindex | ref    | wordid,temp_ix                                                                         | temp_ix | 4       | database1.word.wordid        |    3 | Using index condition                                     |
|  1 | SIMPLE      | postquery | eq_ref | PRIMARY,threadid,showthread                                                            | PRIMARY | 4       | database1.postindex.postid   |    1 | NULL                                                      |
|  1 | SIMPLE      | thread    | eq_ref | PRIMARY,forumid,postuserid,pollid,title,lastpost,dateline,prefixid,tweeted,firstpostid | PRIMARY | 4       | database1.postquery.threadid |    1 | Using index                                               |
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------+---------+---------+------------------------------+------+-----------------------------------------------------------+

Update

LIMIT 25 doesn't seem to be helping much. It shaves off maybe second from a query that normally returns hundreds of results.

Clarification

The part that's slowing down MySQL is the GROUP BY ... HAVING ... bit. With GROUP BY, the LIMIT is pretty much useless for improving performance. Without GROUP BY, and as long as the LIMIT remains, the queries are quite speedy.

SQL Info

Output of SHOW CREATE TABLE postindex;:

CREATE TABLE `postindex` (
  `wordid` int(10) unsigned NOT NULL DEFAULT '0',
  `postid` int(10) unsigned NOT NULL DEFAULT '0',
  `intitle` smallint(5) unsigned NOT NULL DEFAULT '0',
  `score` smallint(5) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `wordid` (`wordid`,`postid`),
  KEY `temp_ix` (`wordid`),
  KEY `postid` (`postid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I didn't make the table, so I have no idea why there's a duplicate index on wordid; however, I'm not willing to delete it, since this is ancient, fickle software.

---

You can try several rewrites and compare execution plan and times.

Using 2 EXISTS subqueries (one for each word to be checked):

SELECT t.threadid
FROM thread AS t
WHERE EXISTS
      ( SELECT 1
        FROM post AS p
          JOIN postindex AS pi
            ON pi.postid = p.postid
          JOIN word AS w
            ON pi.wordid = w.wordid
        WHERE w.title = 'word1'
          AND t.threadid = p.threadid
      )
  AND EXISTS
      ( SELECT 1
        FROM post AS p
          JOIN postindex AS pi
            ON pi.postid = p.postid
          JOIN word AS w
            ON pi.wordid = w.wordid
        WHERE w.title = 'word2'
          AND t.threadid = p.threadid
      ) ;

Using one EXISTS subquery:

SELECT t.threadid
FROM thread AS t
WHERE EXISTS
      ( SELECT 1
        FROM post AS p1
          JOIN postindex AS pi1
            ON  pi1.postid = p1.postid
          JOIN word AS w1
            ON  w1.wordid = pi1.wordid
            AND w1.title = 'word1'

          JOIN post AS p2
            ON  p2.threadid = p1.threadid
          JOIN postindex AS pi2
            ON  pi2.postid = p2.postid
          JOIN word AS w2
            ON  w2.wordid = pi2.wordid
            AND w2.title = 'word2'

        WHERE t.threadid = p1.threadid
          AND t.threadid = p2.threadid
      ) ;

A single query with many joins and GROUP BY only to remove the duplicate threadid:

SELECT t.threadid
FROM thread AS t

  JOIN post AS p1
    ON  p1.threadid = t.threadid
  JOIN postindex AS pi1
    ON  pi1.postid = p1.postid
  JOIN word AS w1
    ON  w1.wordid = pi1.wordid
    AND w1.title = 'word1'

  JOIN post AS p2
    ON  p1.threadid = t.threadid
  JOIN postindex AS pi2
    ON  pi2.postid = p2.postid
  JOIN word AS w2
    ON  w2.wordid = pi2.wordid
    AND w2.title = 'word2'

WHERE p1.threadid = p2.threadid        -- this line is redundant
GROUP BY t.threadid ;

Mysql: Select values that have all ID from array

We have a table film_actor that represents many to many relationship between films and actors.

CREATE TABLE film_actor(
  film_id INT(11) NOT NULL,
  actor_id INT(11) NOT NULL,
  PRIMARY KEY (film_id, actor_id)
);
INSERT INTO film_actor VALUES 
  (1, 5),
  (1, 6),
  (1, 8),
  (1, 10),
  (2, 5),
  (2, 10),
  (2, 15),
  (3, 5),
  (3, 8),
  (3, 10),
  (4, 5),
  (4, 8);

Suppose, we want to find all films where the only actors from a given array { 5, 8, 10 } were starring:

SELECT film_id FROM film_actor
GROUP BY
  film_id
HAVING 
  COUNT(IF(actor_id = 5, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 8, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 10, 1, NULL)) > 0 AND
  COUNT(IF(actor_id <> 5 AND actor_id <> 8 AND actor_id <> 10, 1, NULL)) = 0;
+---------+
| film_id |
+---------+
|       3 |
+---------+

The next query will return the same films:

SELECT film_id, GROUP_CONCAT(actor_id ORDER BY actor_id) AS actors FROM film_actor
GROUP BY
  film_id
HAVING
  actors = '5,8,10';
+---------+--------+
| film_id | actors |
+---------+--------+
|       3 | 5,8,10 |
+---------+--------+

Mysql: Select values that have at least ID from array

We have a table film_actor that represents many to many relationship between films and actors.

CREATE TABLE film_actor(
  film_id INT(11) NOT NULL,
  actor_id INT(11) NOT NULL,
  PRIMARY KEY (film_id, actor_id)
);
INSERT INTO film_actor VALUES 
  (1, 5),
  (1, 6),
  (1, 8),
  (1, 10),
  (2, 5),
  (2, 10),
  (2, 15),
  (3, 5),
  (3, 8),
  (3, 10),
  (4, 5),
  (4, 8);

Suppose, we want to find all films where the actors from a given array { 5, 8, 10 } were starring:

SELECT film_id FROM film_actor
GROUP BY
  film_id
HAVING 
  COUNT(IF(actor_id = 5, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 8, 1, NULL)) > 0 AND
  COUNT(IF(actor_id = 10, 1, NULL)) > 0;
+---------+
| film_id |
+---------+
|       1 |
|       3 |
+---------+

The next query will return the same films:

SELECT film_id, GROUP_CONCAT(actor_id) AS actors FROM film_actor
GROUP BY
  film_id
HAVING
  FIND_IN_SET(5, actors) AND FIND_IN_SET(8, actors) AND FIND_IN_SET(10, actors);
+---------+----------+
| film_id | actors   |
+---------+----------+
|       1 | 5,6,8,10 |
|       3 | 5,8,10   |
+---------+----------+

Mysql: Select where field has more than 2 non duplicated records sql

This should be pretty easy but I am just not able to figure this one out. Got a table with customers order information. There is a field within this table called customers_email_address and customers_id along with a number of other non-essential fields. I need to locate distinct records where the entries in the customers_email_address field are different but unique to the same customers_id. In other words, I need to find out what customers have changed their email address in their account since their account's inception so I can update my email advertising program with their new email address. Something I didn't think of when the site was designed! Maybe an example would be: customers_ID customers_email_address 1 joeschome@hotmail.com 2 frankj@hotmail.com 1 joeschome@hotmail.com 2 frankj@hotmail.com 1 joeschome@yahoo.com 2 frankj@yahoo.com 3 janefr@live.com 3 janefr@live.com 3 janefr@live.com result of query should look like this: customers_id customers_email_address 1 joeschome@hotmail.com 1 joeschome@yahoo.com 2 frankj@hotmail.com 2 frankj@yahoo.com

Solution:
CREATE TABLE orders (`customers_ID` int, `customers_email_address` varchar(21));
   
INSERT INTO orders
    (`customers_ID`, `customers_email_address`)
VALUES
    (1, 'joeschome@hotmail.com'),
    (2, 'frankj@hotmail.com'),
    (1, 'joeschome@hotmail.com'),
    (2, 'frankj@hotmail.com'),
    (1, 'joeschome@yahoo.com'),
    (2, 'frankj@yahoo.com'),
    (3, 'janefr@live.com'),
    (3, 'janefr@live.com'),
    (3, 'janefr@live.com');


SELECT DISTINCT o.customers_id, o.customers_email_address
  FROM orders o JOIN
(
  SELECT customers_id
    FROM orders
   GROUP BY customers_id
  HAVING COUNT(DISTINCT customers_email_address) > 1
) q
    ON o.customers_id = q.customers_id
ORDER BY o.customers_id