Can I improve the performance of this MySQL query by adding an index?

I ran SET GLOBAL slow_query_log = 'ON'; in MySQL and this appeared in the log:

SELECT U.*, DATE_FORMAT(U.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') TIMESTAMP_X, 
IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL 120 SECOND), 'Y', 'N') IS_ONLINE, 
DATE_FORMAT(U.DATE_REGISTER, '%d.%m.%Y %H:%i:%s') DATE_REGISTER, 
DATE_FORMAT(U.LAST_LOGIN, '%d.%m.%Y %H:%i:%s') LAST_LOGIN, 
DATE_FORMAT(U.PERSONAL_BIRTHDAY, '%d.%m.%Y') PERSONAL_BIRTHDAY  
FROM b_user U 
WHERE (1=1 AND (((upper(U.EMAIL) like upper('%uggjplove13@gmail.com%') and U.EMAIL is not null)))) ORDER BY U.ID ASC;

Can I add an index for this query to make it run faster?

explain

+----+-------------+-------+-------+--------------------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys            | key     | key_len | ref  | rows   | Extra       |
|  1 | SIMPLE      | U     | index | ix_b_user_email,email_id | PRIMARY | 4       | NULL | 105308 | Using where |

Table definition:

``
CREATE TABLE b_user (
ID int(18) NOT NULL AUTO_INCREMENT,
TIMESTAMP_X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
LOGIN varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PASSWORD varchar(50) COLLATE utf8_unicode_ci NOT NULL,
CHECKWORD varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
ACTIVE char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
NAME varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
LAST_NAME varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
EMAIL varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
LAST_LOGIN datetime DEFAULT NULL,
DATE_REGISTER datetime NOT NULL,
LID char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
PERSONAL_PROFESSION varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PERSONAL_WWW varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PERSONAL_ICQ varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PERSONAL_GENDER` char(1) COLLATE utf8_unicode_ci DEFAULT NULL

Can you get rid of the leading % in '%uggjplove13@gmail.com%'? If so, that will make a world of difference! Any index starting with EMAIL can be used (assuming you get rid of upper), and the query will be fast.

If not, read on...

FULLTEXT(email), when it can be used, will be very fast. But you may have to build some intelligence into when to use it, and when to resort to LIKE '%...%'. For example, uggjplove13@gmail.com --> MATCH(email) AGAINST('+uggjplove13 +gmail' IN BOOLEAN MODE), but skip the com since it will be ignored anyway. But... xi.li@ab.com cannot use FULLTEXT unless you crank down the min_word_len to 2 (which makes the index much bigger).

(Yes, get rid of the UPPER() calls.)

Remove most of the WORK_ and PERSONAL_ columns and build a table of Locations with those columns (one set, not two sets). Then have WORK_LOCATION and PERSONAL_LOCATION as ids in your table. This will clean up the schema (some), and will help in various minor ways.

The ORDER BY id does not hurt much since there are (probably) very few rows to sort after the WHERE is finished. But then, what is significant about the ID order? However, the excessive use of (255) hurts the sort, since it turns into CHAR(255) (765-bytes) during the sort. Change the VARCHAR limits to realistic values. Also...

SELECT ..., or even SELECT WORK_NOTES ... slows things down because of the TEXT field. ORDER BY would prefer to do the sorting in RAM, but when it sees TEXT, it punts and builds a tmp MyISAM table; this is slower. This leads to the admonition of not using SELECT , especially when you are looking for only non-TEXT columns.

StackExchange Database Administrators Q#112785, answer score: 2