Jump to content


Photo

Optimizing Indexes in Big Tables


  • Please log in to reply
3 replies to this topic

#1 neoform

neoform
  • Members
  • PipPipPip
  • Advanced Member
  • 241 posts
  • LocationMontreal

Posted 30 October 2006 - 06:56 PM

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?

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

+----+-------------+----------------+--------+-----------------------+---------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| 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                                               |
+----+-------------+----------------+--------+-----------------------+---------------+---------+---------------------------------------+------+-----------------------------------------------------------+


kewords (index: keyword_stemmed/id) this table has about 800,000 rows
+--------------+-----------------------+------+-----+---------+----------------+
| 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    |                |
+--------------+-----------------------+------+-----+---------+----------------+

Keywords_items (Indexed: keyword_id/item_id) this table has about 54,000,000 rows
+------------+-----------------------+------+-----+---------+-------+
| 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   |     |         |       |
+------------+-----------------------+------+-----+---------+-------+

Listings (index: city) this table has about 8,100,000 rows
+-----------------+-----------------------+------+-----+---------+----------------+
| 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    |                |
+-----------------+-----------------------+------+-----+---------+----------------+

Newsique.com Social News Network

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 30 October 2006 - 08:28 PM

This may be faster
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

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...lyze-table.html

#3 neoform

neoform
  • Members
  • PipPipPip
  • Advanced Member
  • 241 posts
  • LocationMontreal

Posted 30 October 2006 - 08:42 PM

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

thanks :)
Newsique.com Social News Network

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 October 2006 - 07:29 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users