Jump to content

Archived

This topic is now archived and is closed to further replies.

neoform

Optimizing Indexes in Big Tables

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.