MYSQL limit is slow

Question

I have this query running against 15000 records

SELECT t.*, concat(t.address1, ', ',t.zip, ' ',t.city, ', ',c.countryName ) AS fullAddress
FROM `User` `t` INNER JOIN
     Country c
     ON t.countryCode = c.countryCode
WHERE (userType != -1 AND userType != 1 AND address1 IS NOT NULL AND zip IS NOT NULL AND city IS NOT NULL AND t.countryCode IS NOT NULL
      ) AND
       (concat( t.address1, ', ', t.zip, ' ', t.city, ', ', c.countryName ) regexp '^[0-9]+,? [^,]+, [0-9]+,? [^,]+, [a-zA-Z]+$')
LIMIT 1000

When I remove the limit 1000 part, it returns the result quickly, but with the limit 1000 it takes a lot of time. and phpmyadmin gets stuck

the table structure is as below

CREATE TABLE IF NOT EXISTS `User` (
  `id` bigint(20) NOT NULL,
  `address1` text COLLATE utf8_unicode_ci,
  `mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '1 - female, 2-male, 0 - unknown',
  `zip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `countryCode` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `joinedDate` datetime DEFAULT NULL,
  `signUpDate` datetime NOT NULL COMMENT 'User signed up date',
  `supporterDate` datetime NOT NULL COMMENT 'Date which user get supporter',
  `userType` tinyint(2) NOT NULL DEFAULT '4' COMMENT 'Type of user. 1 - Politician 2 - Supporter 3 - Prospects 4 - Non support 5 - Unknown 6 - Newsletter 7 - Petitioner',
  `signup` tinyint(2) NOT NULL DEFAULT '0' COMMENT 'whether user followed signup process 1 - signup, 0 - not signup',
  `isSysUser` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 - system user, 0 - not a system user',
  `dateOfBirth` date DEFAULT NULL COMMENT 'User date of birth',
  `reqruiteCount` int(11) DEFAULT '0' COMMENT 'User count that he has reqruited',
  `keywords` text COLLATE utf8_unicode_ci COMMENT 'Kewords',
  `delStatus` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0 - active, 1 - deleted',
  `city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `longLat` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Longitude and Latitude',
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



ALTER TABLE `User`
  ADD PRIMARY KEY (`id`),
  ADD KEY `email` (`email`),
  ADD KEY `cindex` (`countryCode`);

How can I improve this query ?

Answer 1

I would think that the following line:

AND (concat( t.address1, ', ', t.zip, ' ', t.city, ', ', c.countryName ) regexp '^[0-9]+,? [^,]+, [0-9]+,? [^,]+, [a-zA-Z]+$')

is causing your bottleneck. I would suggest removing that line and running the query to see how much that improves your performance. If that indeed is the bottleneck, here are some possible actions you could take:

• Validate the data on insert, so all data is valid.
• If your database should accept invalid addresses, you could create a IS_VALID_ADDRESS boolean column.
• You could make one or both of the columns above a GENERATED column and ensure it is STORED