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
upper(U.EMAIL) like upper('%[email protected]%')
- do you need to do this? As you useutf8_unicode_ci
theupper()
part should not be needed, the "ci" stands for "case insensitive". But do you really need LIKE with % at the beginning? - that one has not way to use index.1=1
andemail is not null
is redundant. So I think the query is created automatically by an application. And this application puts a '%' at the beginning and the end of the search string.