Hello Devs,
I use the Roundcube and I like so much, but in 0.5.1 version have a change in rcube_user.php which causes problems in the users table with many records:
SELECT * FROM users WHERE mail_host = '----' AND BINARY username = '---';
This query is too slow when we have more than 200000 records, because using like this the index is not used. I did some tests and the best way is using BINARY before the username(variable of login):
SELECT * FROM users WHERE mail_host = '----' AND username = BINARY '---';
In this case, in "users"."username" have index for use and query is more fast.
Regards,
Thiago Coutinho
obs: The tests were made by Francisco Freire and Thiago Coutinho. _______________________________________________ List info: http://lists.roundcube.net/dev/ BT/aba52c80
On Thu, Feb 10, 2011 at 17:41, Thiago Coutinho thiago@osfeio.com wrote:
Hello Devs,
I use the Roundcube and I like so much, but in 0.5.1 version have a change in rcube_user.php which causes problems in the users table with many records:
SELECT * FROM users WHERE mail_host = '----' AND BINARY username = '---';
This query is too slow when we have more than 200000 records, because using like this the index is not used. I did some tests and the best way is using BINARY before the username(variable of login):
SELECT * FROM users WHERE mail_host = '----' AND username = BINARY '---';
Indeed, moving the BINARY modifier makes mysql use the index again. Confirmed with EXPLAIN. This is now fixed in http://trac.roundcube.net/changeset/4532
Thanks a lot for reporting this!
~Thomas _______________________________________________ List info: http://lists.roundcube.net/dev/ BT/aba52c80
-
Thiago Coutinho -
Thomas Bruederli