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

Question

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('%uggjplov[email protected]%') 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,
  `PERSONAL_BIRTHDATE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_PHOTO` int(18) DEFAULT NULL,
  `PERSONAL_PHONE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_FAX` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_MOBILE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_PAGER` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_STREET` text COLLATE utf8_unicode_ci,
  `PERSONAL_MAILBOX` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_CITY` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_STATE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_ZIP` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_COUNTRY` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_NOTES` text COLLATE utf8_unicode_ci,
  `WORK_COMPANY` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_DEPARTMENT` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_POSITION` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_WWW` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_PHONE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_FAX` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_PAGER` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_STREET` text COLLATE utf8_unicode_ci,
  `WORK_MAILBOX` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_CITY` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_STATE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_ZIP` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_COUNTRY` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `WORK_PROFILE` text COLLATE utf8_unicode_ci,
  `WORK_LOGO` int(18) DEFAULT NULL,
  `WORK_NOTES` text COLLATE utf8_unicode_ci,
  `ADMIN_NOTES` text COLLATE utf8_unicode_ci,
  `STORED_HASH` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `XML_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PERSONAL_BIRTHDAY` date DEFAULT NULL,
  `EXTERNAL_AUTH_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CHECKWORD_TIME` datetime DEFAULT NULL,
  `SECOND_NAME` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CONFIRM_CODE` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LOGIN_ATTEMPTS` int(18) DEFAULT NULL,
  `LAST_ACTIVITY_DATE` datetime DEFAULT NULL,
  `AUTO_TIME_ZONE` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `TIME_ZONE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `TIME_ZONE_OFFSET` int(18) DEFAULT NULL,
  `TITLE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BX_USER_ID` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LANGUAGE_ID` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_login` (`LOGIN`,`EXTERNAL_AUTH_ID`),
  KEY `ix_b_user_email` (`EMAIL`),
  KEY `ix_b_user_activity_date` (`LAST_ACTIVITY_DATE`),
  KEY `IX_B_USER_XML_ID` (`XML_ID`),
  KEY `bdate` (`PERSONAL_BIRTHDATE`),
  KEY `dregister` (`DATE_REGISTER`),
  KEY `email_id` (`EMAIL`,`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=109606 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

Can you get rid of the leading % in '%[email protected]%'? 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, [email protected] --> MATCH(email) AGAINST('+uggjplove13 +gmail' IN BOOLEAN MODE), but skip the com since it will be ignored anyway. But... [email protected] 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.

Answer 2

The answer is NO, you cannot improve this with just an index creation. You should:

• Replace U.* with only the columns you want in your recordset;
• Shift all the formatting and conversion processing effort into the application code;
• Create a full text index on the EMAIL column and rewrite the query to use it.

The relevant documentation on Full Text Indexes, for MySQL 5.6, can be found here: https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

Keep in mind the above comments as they are also quite valid.