MySQL - Query is very slow on no Result (null)
I have a problem with my SQL Query which runs flawlessly fast unless it returns no results.
I have 4 Tables : worlds (2 items), players (about 2000 items), world_chunk (about 16000 items) and world_block (about 1 million items)
SELECT bid,playername FROM worlds JOIN world_chunks ON worlds.id = world_chunks.mainid JOIN world_blocks ON world_chunks.cid = world_blocks.cid JOIN players ON world_blocks.player = players.pid WHERE worldname='world' AND x='-684' AND y='63' AND z='-2234' AND cx ='-43' AND cz='-140'
x,y,z are held in world_blocks and cx,cz are held in world_chunks and worldname in worlds All Indexes are set and it is really fast for everything but empty results.
Is there anyway I could speed up empty results?
Thanks alot for you guys help.
EDIT: Here is the db structure: http://pastebin.com/rxQQ5mzp
Its MySQL InnoDB
EXPLAIN on Emtpy Query: 1 SIMPLE worlds ALL PRIMARY,idx_mainid NULL NULL NULL 2 Using where 1 SIMPLE world_blocks ALL NULL NULL NULL NULL 766845 Using where; Using join buffer 1 SIMPLE world_chunks eq_ref PRIMARY,idx_cid PRIMARY 4 WatchBlock.world_blocks.cid 1 Using where 1 SIMPLE players eq_ref PRIMARY,idx_pid PRIMARY 4 WatchBlock.world_blocks.player 1 EXPLAIN on Found Query: 1 SIMPLE worlds ALL PRIMARY,idx_mainid NULL NULL NULL 2 Using where 1 SIMPLE world_blocks ALL NULL NULL NULL NULL 766845 Using where; Using join buffer 1 SIMPLE world_chunks eq_ref PRIMARY,idx_cid PRIMARY 4 WatchBlock.world_blocks.cid 1 Using where 1 SIMPLE players eq_ref PRIMARY,idx_pid PRIMARY 4 WatchBlock.world_blocks.player 1
The Result can be empty as x,y,z and cx,cz dont match (so if one of each of those is not in db the result of player should be empty)
Philwinkle is right, it isn't particularly appropriate to post an SQL query performance question without posting the appropriate EXPLAINs. You should also also have posted the DDL for the four tables and associated indices. You say "All Indexes are set", however in your follow comment you suggest that the only indices you have are on the primary keys, which is insufficient for the query troubling you.
For a performance issue it is also useful to note which database you are using, and what table types you are using: your tag suggests this is mysql, which means myisam vs innodb is a critical piece of information you fail to provide. Finally, you claim 'empty' results are slow, however you don't explain why the empty results are empty, is it empty because one of the joins was empty, or because one of the coordinates didn't match (and if so, the world_block, or world_chunk coordinates).
If you provided the EXPLAIN data and basic DDL, I wouldn't be writing this as the question would most likely have been answered by now; as it stands, until you do, this question can't reasonably be answered.
The best I can offer is, you probably need to add at least one index covering some or all of the (x,y,z) and (cx,cy) coordinates; but that is just an educated guess.
Thank-you for the EXPLAIN, that helps. As you have no doubt noted already, the full table scan of world_blocks is the most likely culprit. Given the small size of the database, the index you added for (cid,x,y,z) probably did the most to help. A couple of other points however:
If this is an ingest+read database then MyISAM is a reasonable choice, but if you intend to perform online UPDATE or DELETE operations you are much better off sticking with InnoDB. The real benefit of InnoDB is the concurrency/scalability improvements more than referential integrity (although you shouldn't underestimate the importance of that either).
If x,y, and z are always constant in this query, you should consider moving them to the front of the index. In fact if you are using InnoDB, this EXPLAIN suggests the index should be on just (x,y,z) as including cid will only waste buffer cache. If you insist on using MyISAM you should definitely consider using a covering index on those fields referenced by the join: (x,y,z,cid,players) as this will prevent a full-row read until the final projection.
Which brings up my final points: Always run EXPLAIN as soon as you find yourself with unexplained performance quirks; Always run EXPLAIN after your unexplained performance quirks go away, and make sure it makes sense; Index behaviour for MyISAM and InnoDB are almost always different, and optimisations for one are not always optimisations for the other.