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)

Answers


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.

EDIT:

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.


Need Your Help

AJAX request with headers failing

javascript jquery ajax

I'm trying to do an AJAX request to https://developers.zomato.com/api/v2.1/search referring to Zomato API