pzvxcHkE

1Table creation:
2
3drop database if exists llist_test;
4create database llist_test;
5
6use llist_test;
7
8create table `elems` (
9       `id` bigint not null auto_increment,
10       `parent_id` bigint null,
11       `list_id` bigint not null,
12       `data` varchar(255) not null,
13       PRIMARY KEY(`id`),
14       CONSTRAINT parent FOREIGN KEY (`parent_id`) REFERENCES elems (`id`),
15       INDEX (`list_id`)
16) ENGINE = InnoDB;
17
18insert into elems values (0, NULL, 1, 'A1');
19insert into elems values (0, LAST_INSERT_ID(), 1, 'B1');
20insert into elems values (0, LAST_INSERT_ID(), 1, 'C1');
21insert into elems values (0, LAST_INSERT_ID(), 1, 'D1');
22
23insert into elems values (0, NULL, 2, 'A2');
24insert into elems values (0, LAST_INSERT_ID(), 2, 'B2');
25insert into elems values (0, LAST_INSERT_ID(), 2, 'C2');
26insert into elems values (0, LAST_INSERT_ID(), 2, 'D2');
27
28
29Queries w/ explain: 
30
31
32mysql> explain select * from elems where list_id=2;
33+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
34| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
35+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
36|  1 | SIMPLE      | elems | ALL  | list_id       | NULL | NULL    | NULL |    8 | Using where |
37+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
381 row in set (0.00 sec)