CODEWITHSUNDEEP
mysqlsqldatabase
oliverw92
oliverw92

Reputation: 259

Need advice on speeding up this MySQL query

This is the query I am currently executing:

SELECT * FROM `datalog`
WHERE world_id IN (2) 
AND action IN (0,1,2,8,9,10,11,13,14,15)
AND x = -184.0 AND y = 98.0 AND z = 141.0
ORDER BY data_id DESC;

Unfortunately it is taking a long time and don't know why (5 seconds or more with 14 million entries in the database). I have an index on world_id and action (since there are only a max of 7 worlds and 20 actions). How else could i speed up searching?

EDIT - value of explain: SIMPLE datalog ALL NULL NULL NULL NULL 13510263 Using where; Using filesort

Upvotes: 0

Views: 68

Answers (5)

Tim
Tim

Reputation: 8921

I would try a composite index: (actionid, worldid).

Upvotes: 0

Alan Geleynse
Alan Geleynse

Reputation: 25139

Try adding indexes to x, y, and z.

You said that you thought this would not work since they can contain a large range of values.

As long as you are using a table type that supports BTREE indexes (this is the only index type that MyISAM and INNODB support), this should not be true. If you are using a HASH index, that might be the case since it would need to index each value. But with a BTREE index, MySQL is able to quickly sort for a specific value in the index. This is why it is able to use BTREE indexes on queries with comparison operators as well (<, >, etc.)

You can see more here: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

Upvotes: 1

Cfreak
Cfreak

Reputation: 19309

In addition to what @patapizza said, you should only use IN() if you basically have random things to look for so: world_id = 2 and action < 16 will likely help.

You probably need indexes on the other columns you reference in the WHERE statement and probably on data_id as well. Like @AJ said though, post the output of EXPLAIN and it can be determined exactly why it's slow.

Upvotes: 0

Coeffect
Coeffect

Reputation: 8866

No idea if this would work, but have you tried reordering your conditions? x = -184.0 is likely to be faster than action in (...). If MySQL uses short circuiting, this could speed it up.

Upvotes: 0

patapizza
patapizza

Reputation: 2398

You could try replacing your condition on action field by AND action < 16?

Upvotes: 0

Related Questions