[Suggestion] Stop using WHERE LOWER(...)

[Dragons Master]

Hey People,

I've been noticing that after upgrading from v1.3 my forums have been very slow, now i'm not sure what queries ipb ran in v1.3 but after a lil tests I found out that a major slow-down, at least for big forums with lots of traffic and members (over 160,000) is the fact that almost every access to the ibf_members table is done with a "WHERE LOWER(name)=..." or "WHERE LOWER(member_display_name)=...", and after running explain on this queries i found out that when they run MYSQL IGNORES name/member_display_name INDEXES, look at this for example:

mysql> explain SELECT m.name, m.id, m.member_login_key, m.email, m.title, m.mgroup, m.view_sigs, m.view_img, m.view_avs, g.*, c.converge_pass_salt, c.converge_pass_hash FROM ibf_members m LEFT JOIN ibf_groups g ON (m.mgroup=g.g_id) LEFT JOIN ibf_members_converge c ON (c.converge_id=m.id) WHERE LOWER(name)='test';

+-------+--------+---------------+---------+---------+----------+--------+------------+

| table | type   | possible_keys | key	 | key_len | ref	  | rows   | Extra	  |

+-------+--------+---------------+---------+---------+----------+--------+------------+

| m	 | ALL	| NULL		  | NULL	|	NULL | NULL	 | 166164 | where used |

| g	 | eq_ref | PRIMARY	   | PRIMARY |	   4 | m.mgroup |	  1 |			|

| c	 | eq_ref | PRIMARY	   | PRIMARY |	   4 | m.id	 |	  1 |			|

+-------+--------+---------------+---------+---------+----------+--------+------------+

now take a look at it again, this time without the LOWER addition:

mysql> explain SELECT m.name, m.id, m.member_login_key, m.email, m.title, m.mgroup, m.view_sigs, m.view_img, m.view_avs, g.*, c.converge_pass_salt, c.converge_pass_hash FROM ibf_members m LEFT JOIN ibf_groups g ON (m.mgroup=g.g_id) LEFT JOIN ibf_members_converge c ON (c.converge_id=m

.id) WHERE name='test';

+-------+--------+---------------+---------+---------+----------+------+------------+

| table | type   | possible_keys | key	 | key_len | ref	  | rows | Extra	  |

+-------+--------+---------------+---------+---------+----------+------+------------+

| m	 | ref	| name		  | name	|	 255 | const	|	1 | where used |

| g	 | eq_ref | PRIMARY	   | PRIMARY |	   4 | m.mgroup |	1 |			|

| c	 | eq_ref | PRIMARY	   | PRIMARY |	   4 | m.id	 |	1 |			|

+-------+--------+---------------+---------+---------+----------+------+------------+

I don't know what about u but I went over all of IPB files replacing all LOWER(name) with name and LOWER(member_display_name) with member_display_name and server seems to behave much nicely, in fact the only queries that takes more than a second to run now are ones with the LOWER(..) in it.

If there's anything insecure or if I am mistaken here please let me know so I could restore my modified files (backed it all up of course) but until someone proves me wrong or gives me a good reason to put that lower things back i'm gonna stick to not using it.

Cheers,
- Ben Pere

[Sire]

Not sure, but maybe this has been addressed with 2.0 or 2.1 versions ?