Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

Let's say I have 100,000 rows in a table, and each of the rows has a boolean value. Only ten of these rows have a value of 1, the rest are 0.

How would MySQL perform a SELECT on only the rows with a boolean value of 1? Would it have to do a binary search through all the values? Or would it be smart enough to pull out only the ones with a boolean value of 1 without having to search through the entire list?

share|improve this question

migrated from serverfault.com Jul 28 '11 at 15:09

1 Answer

up vote 3 down vote

To see how MySQL is doing something you want to look at the execution plan. More information on MySQL Execution plans at http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html . You should think of SQL as a declarative language, you tell it to do something , and it writes a program to do that. The execution plan shows you the program it wrote.

SQL will be able to look up specific entries when there is an index on the column ( like an index from a book). However, a binary field with a split distribution is a classic example of a column that doesn't have good selectivity, in other words, it isn't going to narrow down the search very much.

share|improve this answer
Nail right on the head. In fact (as a rule of thumb), if an index has a value whose cardinality is greater than 5% of the total number of rows in the table, the MySQL Query Optimizer will rule out using the index and do a full table scan. 5% of Vadoff's table would be 5000 rows. Since he knows the cardinality of the number is 10, then an index would be most desirable for him in this case. BTW @Kyle, +1 !!! – RolandoMySQLDBA Jul 28 '11 at 15:26
1  
@Rolando - the cardinality of a boolean is at most 2 (or 3 if nulls are allowed). In the OPs case this equates to a cardinality of 0.002% of the total number of rows in the table. The question of whether an index or full scan will be used depends on whether MySQL records statistics on how skewed the data is using something like Oracle's histograms. – Jack Douglas♦ Jul 29 '11 at 12:21
@Jack - If the table is MyISAM, a periodic ANALYZE TABLE (if heavy INSERT/SELECT) or OPTIMIZE TABLE (if table is heavy INSERT/SELECT/UPDATE/DELETE [ANALYZE TABLE is included with OPTIMIZE TABLE]) is needed to keep statistics up-to-date. If the table is InnoDB, statistics are always computed on the fly. Even if you manually run ANALYZE TABLE on an InnoDB table, the statistics are overwritten upon execution of the next query against it (mysqlperformanceblog.com/2010/12/09/…) – RolandoMySQLDBA Jul 29 '11 at 14:45
@Jack - It would be nice is InnoDB has histograms like Oracle. Let's petition for that to be included...WAIT Oracle owns InnoDB. We know where that's going. – RolandoMySQLDBA Jul 29 '11 at 14:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.