Mysql – In Mysql 5.5, why is the upper bound of a character range not inclusive

MySQLmysql-5.5

So I'm trying to query on a character range, i.e. all manufacturers with names starting with a or b:

SELECT manufacturer FROM warehouse WHERE manufacturer >= 'a' AND manufacturer <= 'b'

or:

SELECT manufacturer FROM warehouse WHERE manufacturer BETWEEN 'a' and 'b'

For both of these queries, I only get back manus with names starting with a. If I changed the range to a-c, I would get back all with a-b. Why is the upper bound of the range not inclusive? My main issue with this is how can I include all 'Z's in the range. A-Z would not include any z's in this case.

The manual here: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between
… seems pretty self explanatory. Unfortunately things are not working the way they should.

The weird part is, this works when I create a small test table. However it does not work properly on my 15 million row table.

Best Answer

Perhaps you can try REGEXP with the BINARY operator

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[ab]';

if you want a range (say from A-M)

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[A-M]';

case sensitivity ranges

SELECT manufacturer FROM warehouse
WHERE manufacturer REGEXP BINARY '^[A-Ma-m]';

Notice I used the ^ regular expression operator. This searches "from the beginning of the string".

As for your question, why does BETWEEN 'a' and 'b' fail to get all b's?

Look at what you are asking for in the query: Every manu that is >= 'a' and <= 'b'.

Does 'a' return?. If you have a manu named 'a', then yes.
Does 'aa' return?. If you have a manu named 'a', then yes.
Does 'azzzzzzzzzzzzzzz' return?. I think you know the answer to this one.
Does 'b' return?. If you have a manu named 'b', then yes.
Does 'ba' return?. Even if you had a manu named 'ba', then no.

Although inefficient, here is a query that would have worked:

SELECT manufacturer FROM warehouse
WHERE LEFT(manufacturer,1) IN ('a','b');

UPDATE 2013-02-11 15:22 EDT

I have a nice suggestion: Create an index that only stores two characters of manufacturer.

Perhaps this:

ALTER TABLE manufacturer 
ADD INDEX manufacturer_prefix (manufacturer(2));

If you have any index of the entire manufacturer field, drop that index. You can then do effective two-character range searches.

Related Solutions

Mysql – ny way to increase the thesql reserved pool for super users beyond 1

Is there some sort of sane upper limit any one user should never exceed? Maybe your busiest application has at most 20 open connections during peak normal operation. Let's assume you have 20 non root users.

set max_user_connections = 30 (to give a bit of a safe buffer)

set max_connections = max_user_connections * # users + extra buffer, 
   or 30 * 20 + 50 = 650 max connections.

This way your global max connections is safe from every getting reached and there should always be room for you to get in even if one particularly application or user is getting maxed out.

MySQL – Consistent Nonlocking Reads vs. INSERT … SELECT

From the MySQL Documentation

The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:

By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

To use a consistent read in such cases, enable the innodb_locks_unsafe_for_binlog option and set the isolation level of the transaction to READ UNCOMMITTED, READ COMMITTED, or REPEATABLE READ (that is, anything other than SERIALIZABLE). In this case, no locks are set on rows read from the selected table.

Evidently, you do not need stronger locks when just doing reads. Picture copying data into a table using INSERT ... SELECT or CREATE TABLE ... SELECT. The SELECT needs to be a frozen snapshot for loading the table. If it were a moving target, that would require a transaction within a transaction. If you wanted transaction within a transaction behavior, you would need to script that using SAVEPOINT. Otherwise, stronger locks allow for consistent SELECTs for an atomic INSERT.

Doing the simple SELECT allows your DB Session to trust other transactions not to play smoke-and-mirrors with your view of the data. If it has to violate that trust, it will politely lock every row it can to do its work, rather than deceive DB Sessions that are only doing SELECTs into thinking the data is stable when it is, in fact, changing.

No matter which isolation level you pick, none of them is so granular that a simple SELECT gets held up. That's why SELECT ... FOR UPDATE and SELECT ... FROM ... LOCK IN SHARE MODE were invented, so you can be that granular if politeless is not an option.

I wrote about this before : How can I make a select statement get blocked?

Best Answer

UPDATE 2013-02-11 15:22 EDT

Related Solutions

Mysql – ny way to increase the thesql reserved pool for super users beyond 1

MySQL – Consistent Nonlocking Reads vs. INSERT … SELECT

Related Question