mysql - 我如何加速这个 1.7 秒的 Mysql 查询？

Question

我正在用 PHP 编写一个网络应用程序，可以在其中对用户列表进行投票。我用来从表中提取用户的查询非常慢。我怀疑有一种更有效的方法来检查目标用户是否已被活跃用户投票。

SELECT * 
FROM users
WHERE id NOT IN (
    SELECT ratedid
    FROM votes
    WHERE who LIKE 12707264
)
AND picture1 NOT LIKE  ''
AND cp1 <  '10'
AND gender NOT LIKE  'male'
ORDER BY RAND( ) 
LIMIT 1

表格数据如下:

>SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
>SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `notchus_userdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) NOT NULL,
  `username` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `bio` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `picture1` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `cp1` int(100) DEFAULT NULL,
  `picture2` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `picture3` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `friends` blob,
  `relationship_status` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `relationship_interest` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `verified` int(1) NOT NULL,
  `gender` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `birthday` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `hometown` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `citylocation` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `oauth_provider` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `oauth_uid` int(11) NOT NULL,
  `ratchet` int(1) DEFAULT NULL,
  `boss` int(1) DEFAULT NULL,
  `isadmin` int(1) DEFAULT NULL,
  `views` int(10) NOT NULL,
  `reviews` int(10) NOT NULL,
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `isuser` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


Server version: 5.1.70-cll
-- PHP Version: 5.3.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `notchus_userdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `votes`
--

CREATE TABLE IF NOT EXISTS `votes` (
  `uqid` int(11) NOT NULL AUTO_INCREMENT,
  `value` tinyint(4) NOT NULL,
  `picture` int(11) DEFAULT NULL,
  `ratedid` bigint(20) DEFAULT NULL,
  `comment` int(11) DEFAULT NULL,
  `quote` int(11) DEFAULT NULL,
  `who` bigint(20) NOT NULL,
  `votedate` int(11) NOT NULL,
  `control` varchar(100) NOT NULL,
  PRIMARY KEY (`uqid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1084 ;

Answer 1

首先，LIKE只是用来做通配符匹配的。我很惊讶您在对整数执行 LIKE 时没有收到错误消息。话又说回来，MySQL 所做的一切都没有让我感到惊讶。

SELECT * 
FROM users
WHERE id NOT IN (
    SELECT ratedid
    FROM votes
    WHERE who = 12707264
)
AND picture1 <> ''
AND cp1 <  '10'
AND gender <>  'male'
ORDER BY RAND( ) 
LIMIT 1

另请注意，如果 picture1 可以为空字符串，则列中不应有 NOT NULL，而应存储 NULL在没有图片的列中。