neoform Posted October 30, 2006 Share Posted October 30, 2006 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 keywordsINNER 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"))ANDcity = '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] Link to comment https://forums.phpfreaks.com/topic/25613-optimizing-indexes-in-big-tables/ Share on other sites More sharing options...
shoz Posted October 30, 2006 Share Posted October 30, 2006 This may be faster[code]SELECT l.id, l.url_name, l.name FROMlistings AS lWHEREidIN( 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"))ANDcity = '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 Link to comment https://forums.phpfreaks.com/topic/25613-optimizing-indexes-in-big-tables/#findComment-116944 Share on other sites More sharing options...
neoform Posted October 30, 2006 Author Share Posted October 30, 2006 Now that's what i'm talkin' about! :Dthanks :) Link to comment https://forums.phpfreaks.com/topic/25613-optimizing-indexes-in-big-tables/#findComment-116947 Share on other sites More sharing options...
fenway Posted October 31, 2006 Share Posted October 31, 2006 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. Link to comment https://forums.phpfreaks.com/topic/25613-optimizing-indexes-in-big-tables/#findComment-117480 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.