Ar5cL2MY

1mysql index for speed
2SELECT * FROM tablename WHERE mode = 'value1' ORDER BY instruction_id LIMIT 50
3	
4mode  instruction_id
5A     1
6A     3
7A     4
8A     5
9A     10
10A     11
11B     2
12B     8
13B     12
14B     13
15B     14
16C     6
17C     7
18C     9
19C     15
20C     16
21C     17
22	
23ALTER TABLE `tablename` ADD UNIQUE  (`mode`, instruction_id);
24	
25SELECT A.* FROM tablename A JOIN (
26     SELECT instruction_id FROM tablename 
27     WHERE mode = 'value1' 
28     ORDER BY instruction_id LIMIT 50
29     ) B 
30ON (A.instruction_id = B.instruction_id);
31	
32drop table if exists instruction_modes;
33create table instruction_modes
34(
35mode_id smallint unsigned not null,
36instruction_id int unsigned not null,
37primary key (mode_id, instruction_id), -- note the clustered composite PK order !
38unique key (instruction_id)
39)
40engine = innodb;
41	
42select count(*) from instruction_modes;
43+----------+
44| count(*) |
45+----------+
46|  6000000 |
47+----------+
481 row in set (2.54 sec)
49
50select distinct mode_id from instruction_modes;
51+---------+
52| mode_id |
53+---------+
54|       1 |
55|       2 |
56|       3 |
57+---------+
583 rows in set (0.06 sec)
59
60select * from instruction_modes where mode_id = 2 order by instruction_id limit 10;
61+---------+----------------+
62| mode_id | instruction_id |
63+---------+----------------+
64|       2 |              2 |
65|       2 |              3 |
66|       2 |              4 |
67|       2 |              5 |
68|       2 |              6 |
69|       2 |              9 |
70|       2 |             14 |
71|       2 |             25 |
72|       2 |             28 |
73|       2 |             32 |
74+---------+----------------+
7510 rows in set (0.04 sec)