poor sql table indexing causes full table scans

Originally posted by [b]theultramage[/b]
http://www.eathena.ws/board/index.php?autocom=bugtracker&showbug=3289

I have used mysqld's slow query logging feature to generate a list of frequently occurring queries that accessed a large number of rows. This usually means that a full table scan was needed to execute the query, which in turn means that it might be room for improvement. This is achieved by adding indexes on appropriate columns.

Here is a list of slow queries. All measurements were performed on a server with ~1400 on-line chars, 300k chars in database, and several million rows in inventory/cart/storage dbs. There are also numerous recorded performance problems with Ceres CP, however I'm not sure if it is relevant here so I'll skip them.

DELETE FROM `pet` WHERE pet_id IN (SELECT card1 | card2 <<2 FROM `inventory` WHERE char_id = '%d' AND card0 = -256 UNION SELECT card1 | card2 <<2 FROM `cart_inventory` WHERE char_id = '%d' AND card0 = -256)
location: /src/char_sql/char.c, line 1376, char deletion
performance: 1-8 seconds, billions of rows scanned
comment: MySQL 5 is unable to optimize this IN() query, producing a ridiculously inefficient loop over all pets, and re-running the sub-query every time. Suggest either rewriting as a JOIN, or removing entirely in favor of a one-by-one item-based deletion (already done in /charmerge).

DELETE FROM `pet` WHERE `char_id`='%d' AND `incuvate` = '0';
location: /src/char_sql/char.c, line 1372, char deletion
performance: 0.12-0.14 seconds
comment: Missing index on pet.char_id.

DELETE FROM `friends` WHERE `friend_id` = '%d';
location: /src/char_sql/char.c, line 1396, char deletion
performance: 1 second
comment: Missing index on `friend_id`.

SELECT `id`,`send_name`,`send_id`,`dest_name`,`dest_id`,`title`,`message`,`time`,`status`,`zeny`,`amount`,`nameid`,`refine`,`attribute`,`identify`,`card0`,`card1`,`card2`,`card3` FROM `mail` WHERE `dest_id`='%d' AND `status` < 3 ORDER BY `id` LIMIT %d
location: /src/char_sql/int_mail.c, line 40, mail_fromsql
performance: quite bad
comment: Missing index on `dest_id`.

SELECT `party_id` FROM `party` WHERE `name`='%s';
location: /src/char_sql/int_party.c, line 302, party creation check
performance: 0.15-0.30 seconds, 40k rows scanned
comment: Missing index on `name`.

SELECT guild_id FROM `guild` WHERE name='%s';
location: /src/char_sql/int_guild, line 818, guild creation check
performance: 0.15s, 16k rows examined
comment: Missing index on `name`.

SELECT `guild_id` FROM `guild` WHERE `master` = '%s';
location: /src/char_sql/char.c, line 1147, char deletion
performance: 0.15s, 16k rows examined
comment: Missing index on `master`.

DELETE FROM `guild_alliance` WHERE `guild_id` = '%d' OR `alliance_id` = '%d';
location: /src/char_sql/int_guild.c, line 1501, guild break
performance: 0-0.05s
comment: Not sure why it's logged - needs EXPLAIN analysis.

DELETE FROM `mercenary` WHERE `char_id` = '%d';
location: /src/char_sql/int_mercenary.c, line 63, mercenary_owner_delete
performance: 0s
comment: Missing index on `char_id`.

DELETE FROM `ipbanlist` WHERE `rtime` <= NOW();
location: /src/login/ipban_sql.c, line 248, ipban_cleanup
performance: 0s
comment: Missing index on `rtime`.

This post has been edited by theultramage: Jun 24 2009, 08:22 PM