How can I index - optimize this MySQL query?

Question

I have a query like this:

SELECT id, terrain, occupied, c_type 
FROM map 
WHERE x >= $x-$radius 
  AND x <= $x+$radius 
  AND y >= $y-$radius 
  AND y <= $y+$radius 
   ORDER BY 
     x ASC, 
     y ASC

My table looks like this:

CREATE TABLE `map` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `occupied` tinyint(2) NOT NULL DEFAULT '0',
  `c_type` tinyint(4) NOT NULL DEFAULT '0',
  `x` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `terrain` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4_general_ci

I removed all indexes except PRIMARY KEY, because I am unsure how does indexing works with SQL. What can I do to tune this query? Thanks...

This is not a duplicate,check comments!

See https://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work — SolarBear, Mar 15 '17 at 15:54
Possible duplicate of [How do MySQL indexes work?](http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work) — SolarBear, Mar 15 '17 at 15:54
When you're using WHERE statement database search data by fields you're specified. So you can add indexes in 'x' and 'y'. — Alex Slipknot, Mar 15 '17 at 15:55
. @AlexSlipknot Could you show how would the index look like if I want to avoid table scan altogether? Like, in what order should I place x,y,id, occupied column etc so optimizer would use it efficiently? — Metal Castles, Mar 15 '17 at 16:04
@SolarBear That question is a little bit helpfull, however I still don't understand how to implement index for my query. Should I place x and y first,and when the rest of columns in a same order as I define SELECT ... or should x and y be at the end,i.e. First select columns and x,y. And what about if I want to select x and y too? How then should index be structured? I appreciate your help. — Metal Castles, Mar 15 '17 at 16:10
@Metal Castles just create indexes in your table. Leave query as is. — Alex Slipknot, Mar 15 '17 at 16:32
@AlexSlipknot Yes, but in which column order? `x, y, id, terrain, occupied, c_type` or `id, terrain, occupied, c_type, x, y`? — Metal Castles, Mar 15 '17 at 16:34
@AlexSlipknot and OP: just to be clear, index order CAN matter. See https://stackoverflow.com/questions/1252279/mysql-indices-and-order. — SolarBear, Mar 15 '17 at 20:38
Also, indexes with more columns will end up larger and may not be able to fully fit into memory : see https://stackoverflow.com/questions/11748629/how-to-figure-out-if-mysql-index-fits-entirely-in-memory for more info. — SolarBear, Mar 15 '17 at 20:39
@SolarBear - indexes are cached block-by-block. So, whether it "fully fits in memory" is not quite a valid point. — Rick James, Mar 16 '17 at 03:49
@AlexSlipknot - Separate indexes on `x` and `y`: MySQL will use only one index. — Rick James, Mar 16 '17 at 03:51
@MetalCastles - `INDEX(x,y,id)` -- the `id` adds nothing useful. Having all the columns (hence "covering") is potentially good. However, as SolarBear points out, "bulk may slow things down". — Rick James, Mar 16 '17 at 03:53

score 0 · Answer 1 · answered Mar 16 '17 at 03:46

0

The best you can do with that query is

INDEX(x, y)  -- In this order

That will be effective with

WHERE x >= $x-$radius 
  AND x <= $x+$radius

but not for filtering on y.

And it will (probably) avoid the "filesort" for ORDER BY x ASC, y ASC. Note that the index order must match this order.

Provide EXPLAIN SELECT ... for any attempted SELECT.

And, please switch to InnoDB before MyISAM is removed.

Here is a quick cookbook on indexing in MySQL.

answered Mar 16 '17 at 03:46

Rick James

135,179
13
127
222

I created index `gmp (x, y, id, terrain, occupied, c_type)`, and running EXPLAIN on my above query shows id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE map range gmp gmp 8 NULL 1955 Using where; Using index – Metal Castles Mar 16 '17 at 10:22

score 0 · Accepted Answer · answered Mar 16 '17 at 10:31

So, I added index gmp with columns x, y, id, terrain, occupied, c_type and EXPLAIN SELECT displays:

id: 1

select_type: SIMPLE

table: map

type: range

possible_keys: gmp

key: gmp

key_len: 8

ref: NULL

rows: 1955

Extra: Using where; Using index

So, I guess it works now.

How can I index - optimize this MySQL query?

2 Answers2