Optimizing Indexes in Big Tables

[neoform]

ok, so i've got this big query that deals with 3 rather large tables..  I've indexed all appropriate fields, yet the query still takes up to 15 seconds to load..  is there any way to get this going faster?

[code]SELECT
listings.id,
listings.url_name,
listings.name
FROM
keywords
INNER JOIN keywords_items ON keywords.id = keywords_items.keyword_id
INNER JOIN listings ON keywords_items.item_id = listings.id
WHERE
(word_stemmed IN ("advertis", "promot"))
AND
city = '33939'
GROUP BY listings.id
LIMIT 0, 15[/code]

[code]+----+-------------+----------------+--------+-----------------------+---------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table          | type  | possible_keys        | key          | key_len | ref                                  | rows | Extra                                                    |
+----+-------------+----------------+--------+-----------------------+---------------+---------+---------------------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | keywords      | range  | PRIMARY,word_stemmed  | word_stemmed  | 31      | NULL                                  |  20 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | keywords_items | ref    | PRIMARY,keyword_index | keyword_index | 3      | localdirectory.keywords.id            |  66 |                                                          |
|  1 | SIMPLE      | listings      | eq_ref | PRIMARY,city,city_id  | PRIMARY      | 3      | localdirectory.keywords_items.item_id |    1 | Using where                                              |
+----+-------------+----------------+--------+-----------------------+---------------+---------+---------------------------------------+------+-----------------------------------------------------------+
[/code]


kewords (index: keyword_stemmed/id) this table has about 800,000 rows
[code]+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id          | mediumint(8) unsigned | NO  | PRI | NULL    | auto_increment |
| word        | char(30)              | YES  | UNI | NULL    |                |
| word_stemmed | char(30)              | YES  | MUL | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+[/code]

Keywords_items (Indexed: keyword_id/item_id) this table has about 54,000,000 rows
[code]+------------+-----------------------+------+-----+---------+-------+
| Field      | Type                  | Null | Key | Default | Extra |
+------------+-----------------------+------+-----+---------+-------+
| item_id    | mediumint(8) unsigned | NO  | PRI | 0      |      |
| keyword_id | mediumint(8) unsigned | NO  | PRI | 0      |      |
| weight    | smallint(5) unsigned  | NO  |    |        |      |
+------------+-----------------------+------+-----+---------+-------+[/code]

Listings (index: city) this table has about 8,100,000 rows
[code]+-----------------+-----------------------+------+-----+---------+----------------+
| Field          | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| id              | mediumint(8) unsigned | NO  | PRI | NULL    | auto_increment |
| url_name        | char(50)              | YES  | UNI | NULL    |                |
| longitude      | double                | NO  | MUL |        |                |
| latitude        | double                | NO  |    |        |                |
| name            | char(255)            | YES  |    | NULL    |                |
| address        | char(255)            | YES  |    | NULL    |                |
| state          | smallint(5) unsigned  | YES  | MUL | NULL    |                |
| city            | mediumint(8) unsigned | YES  | MUL | NULL    |                |
| zip            | char(6)              | YES  |    | NULL    |                |
| phone          | char(10)              | YES  |    | NULL    |                |
| fax            | char(10)              | YES  |    | NULL    |                |
| email          | char(128)            | YES  |    | NULL    |                |
| website        | char(255)            | YES  |    | NULL    |                |
| description    | text                  | YES  |    | NULL    |                |
| hours          | text                  | YES  |    | NULL    |                |
| payment_options | text                  | YES  |    | NULL    |                |
| locations      | smallint(5) unsigned  | YES  |    | NULL    |                |
| established_on  | date                  | YES  |    | NULL    |                |
| image          | enum('yes','no')      | YES  |    | no      |                |
| keywords        | text                  | YES  |    | NULL    |                |
| added_on        | datetime              | YES  |    | NULL    |                |
| rating          | tinyint(3) unsigned  | YES  |    | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+[/code]

[shoz]

This may be faster
[code]
SELECT
l.id, l.url_name, l.name
FROM
listings AS l
WHERE
id
IN
(
    SELECT
    i.item_id
    FROM
    keywords AS k
    INNER JOIN
    keywords_items AS i
    ON
    k.id = i.keyword_id
    WHERE
    word_stemmed IN ("advertis", "promot")
)
AND
city = '33939'
LIMIT 0, 15
[/code]

You can add an "ORDER BY id" if you're planning on going through the results in some order.

Also do an "ANALYZE TABLE" on each table.
http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

[neoform]

Now that's what i'm talkin' about! :D

thanks :)

[fenway]

Interesting approach, shoz... that's not what came to mind.  Maybe I was misled by that GROUP BY lying around -- but I like it!

You might even be able to tweak it further by separating the IN clause into two statements, depending on the speed; also, there may be a covering index opportunity here, especially useful if you do add the ORDER BY.