mysql uses primary key instead of index -

i have pretty large table few million rows:

id (primary) countrycode status flag_cc 

i tried following sql statement, quite slow:

select id, countrycode, status, flag_cc table id>=200000 , countrycode=3 , status=1 , flag_cc=0 

so thought idea add index fasten query up:

add index myindex(id, countrycode, status, flag_cc) 

then queried:

explain select id, countrycode, status, flag_cc table id>=200000 , countrycode=3 , status=1 , flag_cc=0 

but mysql wants use primary key instead of key. used force index , compared primary key key.. sadly primary key lot faster.

how be? , ever possible optimize query if primary key slow?

your question "what index?". might want consider reading on them in mysql documentation, here on stackoverflow , using search engine.

consider index index in big encyclopedia. there lot of topics defined, index helps find you're looking little faster.

but should in index? category (science, entertainment, people, ...)? when found category, there's still plenty of articles fall inside each category. there 10 000 articles total of 1000 in science category. if looking science-ey, still leaves 1000 articles through exact article. in database terms, index not have cardinality: it's nice if you've got nothing else not specific enough really speed things up. same hold index starting letter (26 letters in alphabet using index divide number of articles 26, isn't specific).

in databases, means primary key field index: 1 value of field corresponds 1 value in data, once index used find it, there's nothing left through; you've found specific record.

a true/false flag on other hand, divides data in maximum of 2 groups, still leaves plenty of data through after using index.

there exceptions, of course. example, table true/false column. bad column index. it's possibly know 0.01% of records have value 'true' column , query looks true values, never false values. in case, true/false column column index.

then there's range problem: you're not searching specific id, whole range of them, if id unique, still mark whole section of index (and data) 'things still through after using index'. while has cardinality, may not best index use specific query.

another problem mysql cannot through multi-column index when you're not searching on first column of index. index (id, countrycode, status, flag_cc) mean mysql still has start using index id, in query range condition, , previous paragraph explains why that's bad. after applying id part of index can start countrycode part, if mysql determines that's still worth effort. why mysql wants use primary key index though you've given option.

applying information on table: clause contains columns, build index starting column has highest cardinality (the different values) , not used range clause (so not id). if flag_cc contains plenty of different values, use that. if status or countrycode contains more of different values, use 1 of those. depending on how specific first column index is, indexing single column may enough. if not, try adding column next-best cardinality index, et cetera.

and of course, remember indexes (usually, not always) speed lookups, slow down updates, inserts , deletes!

so see, it's not simple problem. consider things i've outlined tip of indexing ice berg.

sources:
http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/
https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html