Page 2 of 2

Posted: Tue Jan 30, 2007 3:27 pm
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.

Posted: Wed Jan 31, 2007 10:30 am
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?

Posted: Wed Jan 31, 2007 3:08 pm
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'];

Posted: Wed Jan 31, 2007 3:25 pm
by WanamakerStudios
Ok. That makes more sense to me! Thanks! Now how would I apply that to a LEFT JOIN?

Posted: Wed Jan 31, 2007 3:52 pm
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.