MySQL & nested set: slow JOIN (not using index)

Question

I have two tables:

localities:

CREATE TABLE `localities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` varchar(30) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_localities_on_parent_id_and_type` (`parent_id`,`type`),
  KEY `index_localities_on_name` (`name`),
  KEY `index_localities_on_lft_and_rgt` (`lft`,`rgt`)
) ENGINE=InnoDB;

locatings:

CREATE TABLE `locatings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `localizable_id` int(11) DEFAULT NULL,
  `localizable_type` varchar(255) DEFAULT NULL,
  `locality_id` int(11) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_locatings_on_locality_id` (`locality_id`),
  KEY `localizable_and_category_index` (`localizable_type`,`localizable_id`,`category`),
  KEY `index_locatings_on_category` (`category`)
) ENGINE=InnoDB;

localities table is implemented as a nested set.

Now, when user belongs to some locality (through some locating) he also belongs to all its ancestors (higher level localities). I need a query that will select all the localities that all the users belong to into a view.

Here is my try:

select distinct lca.*, lt.localizable_type, lt.localizable_id 
from locatings lt
join localities lc on lc.id = lt.locality_id
left join localities lca on (lca.lft <= lc.lft and lca.rgt >= lc.rgt)

The problem here is that it takes way too much time to execute.

I consulted EXPLAIN:

+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
| id | select_type | table | type   | possible_keys                   | key     | key_len | ref                              | rows  | filtered | Extra           |
+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
|  1 | SIMPLE      | lt    | ALL    | index_locatings_on_locality_id  | NULL    | NULL    | NULL                             |  4926 |   100.00 | Using temporary |
|  1 | SIMPLE      | lc    | eq_ref | PRIMARY                         | PRIMARY | 4       | bzzik_development.lt.locality_id |     1 |   100.00 |                 |
|  1 | SIMPLE      | lca   | ALL    | index_localities_on_lft_and_rgt | NULL    | NULL    | NULL                             | 11439 |   100.00 |                 |
+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

The last join obviously doesn’t use lft, rgt index as I expect it to. I’m desperate.

UPDATE: After adding a condition as @cairnz suggested, the query takes still too much time to process.

UPDATE 2: Column names instead of the asterisk

Updated query:

SELECT DISTINCT lca.id, lt.`localizable_id`, lt.`localizable_type` 
FROM locatings lt FORCE INDEX(index_locatings_on_category)
JOIN localities lc
    ON lc.id = lt.locality_id
INNER JOIN localities lca
    ON lca.lft <= lc.lft AND lca.rgt >= lc.rgt
WHERE lt.`category` != "Unknown";

Updated EXAPLAIN:

+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
| id | select_type | table | type   | possible_keys                           | key                         | key_len | ref                             | rows  | filtered | Extra                                           |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
|  1 | SIMPLE      | lt    | range  | index_locatings_on_category             | index_locatings_on_category | 153     | NULL                            |  2545 |   100.00 | Using where; Using temporary                    |
|  1 | SIMPLE      | lc    | eq_ref | PRIMARY,index_localities_on_lft_and_rgt | PRIMARY                     | 4       | bzzik_production.lt.locality_id |     1 |   100.00 |                                                 |
|  1 | SIMPLE      | lca   | ALL    | index_localities_on_lft_and_rgt         | NULL                        | NULL    | NULL                            | 11570 |   100.00 | Range checked for each record (index map: 0x10) |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+

Any help appreciated.

Answer 1

Ah, it just occurred to me.

Since you are asking for everything in the table, mysql decides to use a full table scan instead, as it deems it more efficient.

In order to get some key usage, add in some filters to restrict looking for every row in all the tables anyways.

Updating Answer:

Your second query does not make sense. You are left joining to lca yet you have a filter in it, this negates the left join by itself. Also you're looking for data in the last step of the query, meaning you will have to look through all of lt, lc and lca in order to find your data. Also you have no index with left-most column 'type' on locations, so you still need a full table scan to find your data.

If you had some sample data and example of what you are trying to achieve it would perhaps be easier to help.

Answer 2

try to experiment with forcing index - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html, maybe it's just optimizer issue.