[E] Inefficient table design and query

[bold84]

SELECT * FROM ossn_users WHERE(LOWER(username) = LOWER('S'))

You don't need to lower the column name... So, it should look like this:

SELECT * FROM ossn_users WHERE(username = LOWER('S'))

---

Missing indexes on highly frequented tables...

ALTER TABLE ossn_users MODIFY username VARCHAR(32);
ALTER TABLE ossn_users MODIFY password VARCHAR(64);
ALTER TABLE ossn_users ADD INDEX index_username (username);

ALTER TABLE ossn_components ADD INDEX index_com_id (com_id);
ALTER TABLE ossn_components ADD INDEX index_active (active);

ALTER TABLE ossn_likes ADD INDEX index_subject_id_guid_type (subject_id,guid,type);
ALTER TABLE ossn_likes ADD INDEX index_subject_id_type (subject_id,type);

Kind regards,

Ben

[lianglee]

@ordisbold thank you for the indexes improvement.

Regarding LOWER(username) I think the current implementation it will lower the actual value in column instead of the name and will match with the LOWER(supplied value)

[bold84]

@lianglee the argument that is passed to the LOWER() function is the column name and the returned value is therefore the lowered column name, not the table entry.

Have a look:
https://www.tutorialspoint.com/lower-case-column-names-with-mysql-select

You can also check it by adding the mentioned indexes to the ossn_users table and prepend EXPLAIN to each of the SELECT queries. The one with LOWER() can't use any ones.

[lianglee]

See:

https://stackoverflow.com/a/16082874
https://alvinalexander.com/sql/sql-select-case-insensitive-query-queries-upper-lower/
https://stackoverflow.com/a/2876802

The one disadvantage i see for current implementation is it will search entire table and won't use the indexes :) however need to verify this.

Besides that this is also a solution : https://www.w3schools.com/sql/func_mysql_binary.asp

I'll verify your method soon in depth.

[bold84]

Just lower the usernames and email addresses in PHP before inserting into the table. Problem solved.

[lianglee]

Just lower the usernames and email addresses in PHP before inserting into the table. Problem solved.

Yes it can work but I see many members of community manually playing around users table which is not recommended though, We need to take care of it,

However in next upgrade I'll make emails lowercase on PHP side. Best place would be : https://github.com/opensource-socialnetwork/opensource-socialnetwork/blob/v5.x/classes/OssnUser.php#L68 😊