The state field in the MySQL table design

Design of MySQL chart often have a field state, assuming the value in (0,1,2,3), in this case the SQL statement
SQL * from BT where state=0 or state=1 in millions of data, the speed is very slow.
UNION efficiency is not high.
Do you have experience of it, how to deal with this situation to meet you.?

Started by Nathaniel at November 29, 2016 - 11:00 AM

Built on the state index is not?
select * from bt where state=0
union all
select * from bt where state=1

Posted by Julie at December 02, 2016 - 11:21 AM

Range (0,1,2,3) to establish a single field index has no meaning.?

Posted by Nathaniel at December 16, 2016 - 12:11 PM

Mainly in order to prevent a full table scan.?

Posted by Nathaniel at December 25, 2016 - 12:40 PM

Take millions of data how do not high efficiency

Posted by Miranda at January 02, 2017 - 3:12 PM

where state=0 or state=1

where state=0

Because MYSQL thinks the records of more than 30%, MYSQL will think there's no need to go to the index.

Posted by Camille at January 06, 2017 - 2:26 PM

Indeed, the range of (0,1,2,3) 0 and 1 probability of more than 2,3!

Posted by Nathaniel at January 07, 2017 - 1:15 PM

You really don't know what can be optimized, and millions of data could soon not, plus the index the optimizer would not choose, but increases the maintenance index of consumption

Posted by Tyler at January 13, 2017 - 2:00 PM