Extremely Slow Binary Search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post by WaldoMonster »

I had the same problem when comparing directory/file names.
On a *nix platform you can have test.flac and Test.flac in the same directory.
So I first used this slow query:

Code: Select all

... WHERE BINARY relative_file = "' . mysql_real_escape_string($relative_file) . '"');
It seems that for a BINARY compare the index doesn't work.
So when you first do a normal compare that uses an index and then use the BINARY compare it will speed up.
MySQL goes from left to right with logical operators (the same as PHP) so it narrows the search results for BINARY compare.

Code: Select all

... WHERE relative_file  = "' . mysql_real_escape_string($relative_file) . '"
AND BINARY relative_file  = "' . mysql_real_escape_string($relative_file) . '"');
On my system it dramatically improved the speed.
Hope it will work out on your system to.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

So ... let me know if I am understanding you correctly. I should make the column BINARY, index it and add the BINARY string to my SQL Statement?
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post by WaldoMonster »

WanamakerStudios wrote:So ... let me know if I am understanding you correctly. I should make the column BINARY, index it and add the BINARY string to my SQL Statement?
Here is a complete example:
Make a table "test" and a field "name" VARCHAR and INDEX this field.
Add the values "Ax" and "AX" to the "name" field.

Code: Select all

$query = mysql_query('SELECT name FROM test WHERE name = "Ax"');
while ($result = mysql_fetch_array($query))
    echo $result['name'];
Will result in "Ax" and "AX"

Code: Select all

$query = mysql_query('SELECT name FROM test WHERE BINARY name = "Ax"');
while ($result = mysql_fetch_array($query))
    echo $result['name'];
The BINARY query will only find "Ax" and NOT "AX"


Because BINARY doesn't uses the index you can improve the speed with:

Code: Select all

$query = mysql_query('SELECT name FROM test WHERE name = "Ax" AND BINARY name = "Ax"');
while ($result = mysql_fetch_array($query))
    echo $result['name'];
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Ok. That makes more sense to me! Thanks! Now how would I apply that to a LEFT JOIN?
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post by WaldoMonster »

WanamakerStudios wrote:Ok. That makes more sense to me! Thanks! Now how would I apply that to a LEFT JOIN?
Sorry I can't help you with that.
My knowledge is limited to equi-join.
Post Reply