4

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
Improve this question
6
  • What version of mysql?
    – jkavalik
    Sep 1, 2015 at 8:01
  • 2
    upper(U.EMAIL) like upper('%[email protected]%') - do you need to do this? As you use utf8_unicode_ci the upper() 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.
    – jkavalik
    Sep 1, 2015 at 8:03
  • 1
    1=1 and email 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.
    – miracle173
    Sep 1, 2015 at 8:18
  • yes. this is CMS bitrix.
    – das-ich
    Sep 1, 2015 at 8:24
  • thanks. I understood. it is necessary to tell php developers to alter the code
    – das-ich
    Sep 1, 2015 at 8:26

2 Answers 2

Reset to default
2

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.

Improve this answer
0
-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.

Improve this answer
4
  • 1
    I am not sure you can use fulltext index to search emails (the @ sign etc) but it might return few enough rows to make the LIKE viable.
    – jkavalik
    Sep 1, 2015 at 10:59
  • That situation does not appear to be a restriction: dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html Nevertheless, mind that "The '%' character is not a supported wildcard character for full-text searches."
    – Ozzie
    Sep 1, 2015 at 11:13
  • I don't see how a full text search will help here. And even if it does, the answer does not provide how the query should be rewritten with MATCH.
    – ypercubeᵀᴹ
    Sep 1, 2015 at 11:25
  • Also, consider removing the order by ID if it's not relevant as it adds processing load on the database server since it will sort the records before returning the result.
    – Ozzie
    Sep 1, 2015 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.