database - MySQL Query Optimization, Primary Key Index is not used? -

we have lot of question this, each query unique question arise. have following query

select * tblretailerusers (company_id=169 or company_id in (select id tblretailercompany multi_ret_id='169')) , ( id in (select contact_person_1 tbllocations status=1 , (retailer_comp_id=169 or retailer_comp_id in (select id tblretailercompany multi_ret_id='169'))) or id in(select contact_person_2 tbllocations status=1 , (retailer_comp_id=169 or retailer_comp_id in (select id tblretailercompany multi_ret_id='169'))) ) , (last_login not null ) 

it has 3 tables involve, retailer, location , user. standard user information. each retailer can have child retailer, retailer table has parent retailer id. each table has 6k records, , table has primary key auto increment , know indexed well. in user table email field indexed.

now, query take < 1 sec fine have, client want find user whose' email ids start specific letter, a , b. add query, starts taking 50-60 seconds. create index on email field not unique, , new query looks like

select * tblretailerusers (company_id=169 or company_id in (select id tblretailercompany multi_ret_id='169')) ,  ( id in (select contact_person_1 tbllocations status=1 , (retailer_comp_id=169 or retailer_comp_id in (select id tblretailercompany multi_ret_id='169')))  or      id in(select contact_person_2 tbllocations status=1 , (retailer_comp_id=169 or retailer_comp_id in (select id tblretailercompany multi_ret_id='169'))) ) , (last_login not null ) , email  regexp '^a|^b'  

i try use explain, in both version of query , interesting fact notice in primary table row not show value possible_key using primary key id search in user table have index on email field too. here explain of query:

i try recreate index, current have index use id, companyid , email in 1 index other primary key in user table. create index on company tables, nothing speeds up. user table myisam.

my question is, how can skip sub query child company search, can see used thrice in above query.

edit: reason using regexp in query when try like 'a%' slow option.

edit test last_login null instead of last_login not null, , results take less 5 seconds. aren't null or not null similar?

instead of using regex:

and email  regexp '^a|^b'  

...try simple like:

and (email 'a%' or email 'b%') 

regular expressions bit of heavy rather small comparison. far faster. also, wouldn't expect optimiser try decode regexp trying do. like, however, knows , use index you've set up.