Slow query character_inventory

[voidx]

Hello, I have a problem with some slow queries, I think that could be optimized, one of them is:

SELECT data,text,bag,slot,item,item_template FROM character_inventory JOIN item_instance ON character_inventory.item = item_instance.guid WHERE character_inventory.guid = '931729' ORDER BY bag,slot;

Config

innodb_file_per_table=1

innodb_additional_mem_pool_size = 60M

innodb_buffer_pool_size = 12096M

innodb_data_file_path = ibdata1:512M:autoextend

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 0

innodb_log_buffer_size = 32M

innodb_log_file_size = 1024M

innodb_log_files_in_group = 2

innodb_adaptive_flushing = 1

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 60

innodb_flush_method=O_DSYNC

innodb_table_locks=0

innodb_file_format=Barracuda

#innodb_data_home_dir = <directory>

#innodb_force_recovery=1

#innodb_fast_shutdown

#innodb_log_group_home_dir

innodb_io_capacity=1500

Raid 10 SAS

Mysql 5.5

Some idea? This query is big problem on mysql servers.

[antiroot]

Your 'ORDER BY bag,slot' is causing MySQL to do a filesort (which is bad) you can remove the order by completely and it will be faster, but if you really want to sort by those fields create a new index using guid,bag,slot which will get rid of the filesort.

Heres some quick tests on my server (note: my database has very little data in it, used SQL_NO_CACHE for testing)

(74 total, Query took 0.0029 sec) - No Indexes

(74 total, Query took 0.0013 sec) - No ORDER BY

(74 total, Query took 0.0014 sec) - With guid,bag,slot index

[voidx]

Why not join the two tables? item_intance and character_inventory? ...

[antiroot]

Good question, the only data that would have to be merged is item_instance.data into the character_inventory table (this would require many changes to source code for the core) but if your indexes are setup correctly selecting from two joined tables should be close to as fast as a single

I do see in my DB there a few records in item_instance that don't have matching records in character_inventory (not sure whats going on with that), but yes theres some redundant data floating around between the two that could be optimized if they were in one table

[voidx]

my item_instance=1.2gb and character_inventory=400mb....

[hunuza]

item_instance is not only used by character_inventory but also by guild_bank_item, auctionhouse, mail_items...

[antiroot]

@hunuza Thanks for pointing that out, my server only has 3 active users on it and we have not used the guildbank, AH, or mail. But that makes perfect sense why item_instance is its own table now that you mentioned that

@voidx Did you try the two suggestions I made about either dropping your ORDER BY in your query or adding the index? With your two tables being that large it makes sense why that query is slow because its having to do the filesort when you use the ORDER BY

If you do not want to edit your DB structure and this query is returning to a PHP or other scripting language you can easily use that language to sort your results by the bag and slot so that mysql doesn't have to