Mysql – A question on MySQL SELECT Algorithm/Function

MySQL

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?

Best Answer

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.

Related Solutions

Mysql – I would like to query a range of criteria on multiple columns in MySQL

Assuming you want to match against 1 1 1 you could do something like this:

set @fast:=1;
set @smart:=1;
set @sharp:=1;

SELECT if(`fast`=@fast,1,0) + if(`smart` =@smart,1,0) + if(`sharp`=@sharp,1,0) as score FROM `table` order by score DESC

That should return the exact matches with score 3, people with 2 matches with score 2 and so on.

Database Design – Best Logical Structure for Different Entity Categories

I recommend you to read about the dangers of the Entity-Attribute-Value pattern on this presentation by @Bill Karwin.

One of the solutions is storing NoSQL-like data in a serialized BLOB, if you do not need to read and write individual properties. That is, storing a key (product) as the primary key, and the variable property-value pairs all together in a single blob. This is a non-normalized structure, leaving the responsibility of the constraints to the application layer, but it works great for certain patterns (the same that usually work well with NoSQL solutions). Even if you need indexing, you can create inverted indexes on a separate table as needed.

This and other solutions can be seen on this presentation in the latest MySQL Conference, including when you should search for alternative database engines.

Best Answer

Related Solutions

Mysql – I would like to query a range of criteria on multiple columns in MySQL

Database Design – Best Logical Structure for Different Entity Categories

Related Question