Using Indexes in mysql do not improve query time -

i have sql query:

select frage_id  session_fragen  (     userantwort1 = 0      , userantwort2 = 0      , userantwort3 = 0      , userantwort4 = 0      , userantwort5 = 0 ) , session_id = 17898  order sessionfrage_id asc limit 1; 

in beginning query run slow. added index improve speed. bevor using indexes scanned approx. 500.000 rows ... after using indexes reduced rows approx. 550 (depends on results) query still takes more 2 sec. run.

i hope has improvementtips me...

tanks lot!

---

sql explain:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    1   simple  session_fragen  index   session_fragen_big_index_2,session_fragen_big_inde...   primary     8   null    535     using 

---

show indexes:

session_fragen  0   primary     1   sessionfrage_id       626229  null    null        btree    session_fragen  1   frage_id    1   frage_id      3479    null    null        btree    session_fragen  1   session_fragen_big_index_2  1   userantwort1      2   null    null        btree    session_fragen  1   session_fragen_big_index_2  2   userantwort2      3   null    null        btree    session_fragen  1   session_fragen_big_index_2  3   userantwort3      5   null    null        btree    session_fragen  1   session_fragen_big_index_2  4   userantwort4      7   null    null        btree    session_fragen  1   session_fragen_big_index_2  5   userantwort5      9   null    null        btree    session_fragen  1   session_fragen_big_index    1   session_id    16057   null    null        btree    session_fragen  1   session_fragen_big_index    2   frage_id      626229  null    null        btree    session_fragen  1   session_fragen_big_index    3           sessionfrage_id       626229  null    null        btree    session_fragen  1   userantwort1    1   userantwort1      2   null    null        btree    session_fragen  1   userantwort2    1   userantwort2      2   null    null        btree    session_fragen  1   userantwort3    1   userantwort3      2   null    null        btree    session_fragen  1   userantwort4    1   userantwort4      2   null    null        btree    session_fragen  1   userantwort5    1   userantwort5      2   null    null        btree    session_fragen  1   session_id  1   session_id    16057   null    null        btree 

---

table structure:

session_fragen  create table `session_fragen` (  `sessionfrage_id` bigint(20) not null auto_increment,  `user_id` int(7) not null,  `sf_timestamp` timestamp not null default current_timestamp,  `session_id` int(11) not null,  `frage_id` int(11) not null,  `userantwort1` tinyint(1) not null default '0',  `userantwort2` tinyint(1) not null default '0',  `userantwort3` tinyint(1) not null default '0',  `userantwort4` tinyint(1) not null default '0',  `userantwort5` tinyint(1) not null default '0',  primary key (`sessionfrage_id`),  key `frage_id` (`frage_id`),  key `session_fragen_big_index_2`         (`userantwort1`,`userantwort2`,`userantwort3`,`userantwort4`,`userantwort5`),  key `session_fragen_big_index` (`session_id`,`frage_id`,`sessionfrage_id`),  key `userantwort1` (`userantwort1`),  key `userantwort2` (`userantwort2`),  key `userantwort3` (`userantwort3`),  key `userantwort4` (`userantwort4`),  key `userantwort5` (`userantwort5`),  key `session_id` (`session_id`) ) engine=myisam auto_increment=862095 default charset=utf8 

i recommend following index query:

alter table session_fragen add index sf_sess_fragen_id (   session_id,    userantwort1,    userantwort2,   userantwort3,   userantwort4,   userantwort5,   sessionfrage_id,   frage_id ); 

my reasons are:

• first add columns referenced in equality predicates. put session_id column first because it's narrow down search quickly.

• after columns, add sessionfrage_id column sorting. once search has found rows conditions in where clause, rows in tied order. order determined next column of index, , mysql's optimizer use order instead of having sort matching rows.

• finally, add frage_id column select-list. used neither searching nor sorting, including in index creates covering index.

i tested without reasonable test data, optimizer didn't automatically use new index. if use index hint coax use new index, better explain report (the index hint may unnecessary if run on real table).

mysql> explain select frage_id session_fragen use index (sf_sess_fragen_id)   (userantwort1 = 0 , userantwort2 = 0 , userantwort3 = 0      , userantwort4 = 0 , userantwort5 = 0 )    , session_id = 17898 order sessionfrage_id asc limit 1\g *************************** 1. row ***************************            id: 1   select_type: simple         table: session_fragen          type: ref possible_keys: sf_sess_fragen_id           key: sf_sess_fragen_id       key_len: 9           ref: const,const,const,const,const,const          rows: 1         extra: using where; using index 

good things explain:

• rows nice , low, because session_id narrowed down search.
• using index indicates index-only query (i.e. covering index), means query doesn't have read table rows @ all.
• we don't see type: index (index scan), nor see using filesort.

see presentation how design indexes, really more guidelines on designing indexes.