Query optimization

Question

SELECT nar.name, nar.reg, stat.lvl
FROM members AS nar
JOIN stats AS stat 
ON stat.id = nar.id
WHERE nar.ref = 9

I have indexes on id in both tables and I have index referavo either. But still, it checks all rows in stats table (I use Explain to get this information), but in members table it checks only one row how it supposed to be. What's wrong with stats table? Thank you very much.

CREATE TABLE `members` (
 `id` int(11) NOT NULL
 `ref` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT


CREATE TABLE `stats` (
 `id` int(11) NOT NULL AUTO_INCREMENT
 PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC



id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stat ALL PRIMARY NULL NULL NULL 22  
1 SIMPLE nar eq_ref PRIMARY PRIMARY 4 table_nme.stat.id 1 Using where

Answer 1

Your tables are ridiculously small - just 23 rows is tiny.

MySQL chooses different query plans depending on how many rows there are in the table and based on how many it estimates will be selected (from the statistics). You should performance test your queries with realistic data - both the amount of data and the distribution of values in the data should be as realistic as possible. Otherwise the query plan MySQL chooses in testing might not be the same the actual query plan for your live system.

Your tables are so small that using an index could be slower than just checking the table directly. Remember that checking data that is already in memory is fast, but reads are slow. Accessing an index can require an extra read - first the index has to be fetched and read to find which rows to select, then if your index isn't a covering index the relevant rows in the table have to be fetched and read to get the values that aren't in the index. MySQL is perfectly entitled to not use an index even if one is available if it believes that doing so will result in a slower plan.

Put some more rows in your table (thousands) and try running EXPLAIN again. You will probably find that when you have more rows that the PRIMARY KEY index will be used for the join.

Answer 2

MySQL can use only one index at a time per table, thus it sees the member row using the index, and then performs a sequential search for the ID.

You have to create a multi columns index for the members table

 CREATE INDEX idref ON members(id,ref);

please try the reverse one as well if it doesn't get better (first: drop index idref on members)

 CREATE INDEX idref ON members(ref,id);

(I cannot try it myself now)