Query performance problem.

Question

Hello everyone, I'm having troubles with certain query performance, i have the following 2 tables:

CREATE TABLE `customers` (
  `CustFullName` varchar(45) NOT NULL,
  `CustPassword` varchar(45) NOT NULL,
  `CustEmail` varchar(128) NOT NULL,
  `SocialNetworkId` tinyint(4) NOT NULL,
  `CustUID` varchar(64) CHARACTER SET ascii NOT NULL,
  `CustMoney` bigint(20) NOT NULL DEFAULT '0',
  `LastIpAddress` varchar(45) CHARACTER SET ascii NOT NULL,
  `LastLoginTime` datetime NOT NULL DEFAULT '1900-10-10 10:10:10',
  `SmallPicURL` varchar(120) CHARACTER SET ascii DEFAULT '',
  `LargePicURL` varchar(120) CHARACTER SET ascii DEFAULT '',
  `LuckyChips` int(10) unsigned NOT NULL DEFAULT '0',
  `AccountCreationTime` datetime NOT NULL DEFAULT '2009-11-11 11:11:11',
  `AccountStatus` tinyint(4) NOT NULL DEFAULT '1',
  `CustLevel` int(11) NOT NULL DEFAULT '0',
  `City` varchar(32) NOT NULL DEFAULT '',
  `State` varchar(32) NOT NULL DEFAULT '0',
  `Country` varchar(32) NOT NULL DEFAULT '',
  `Zip` varchar(16) CHARACTER SET ascii NOT NULL,
  `CustExp` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`CustUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And:

CREATE TABLE `mutualfriends` (
  `CustUID` varchar(32) CHARACTER SET ascii NOT NULL,
  `CustUID2` varchar(32) CHARACTER SET ascii NOT NULL,
  `FType` tinyint(4) NOT NULL,
  PRIMARY KEY (`CustUID`,`CustUID2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

customers table contains 1M rows and mutalfriends about 50k rows.

I need the results of the following query:

SELECT c.CustUID, c.CustFullName, c.CustMoney, c.SmallPicURL
FROM `customers` c
WHERE c.`CustUID` = '9:2'
OR c.`CustUID` IN
(SELECT m.CustUID2 FROM mutualfriends m WHERE m.CustUID = '9:2');
OR c.`CustUID` IN
(SELECT m.CustUID FROM mutualfriends m WHERE m.CustUID2 = '9:2');

for some reason i don't understand, this query takes about 10 secconds to finish. The sub queries contains no more than 3 rows each, if i put constants instead of:

(SELECT m.CustUID2 FROM mutualfriends m WHERE m.CustUID = '9:2');

And:

 (SELECT m.CustUID FROM mutualfriends m WHERE m.CustUID2 = '9:2');

For example:

 SELECT c.CustUID, c.CustFullName, c.CustMoney, c.SmallPicURL
    FROM `customers` c
    WHERE c.`CustUID` = '9:2'
    OR c.`CustUID` IN
    ('9:3','9:4','9:5');
    OR c.`CustUID` IN
    ('9:6','9:7');

Then The query takes a few ms to finish. What am i doing wrong here this query shouldn't take more than a few ms...

also i may add that this part of the query:

(SELECT m.CustUID2 FROM mutualfriends m WHERE m.CustUID = '9:2');
(SELECT m.CustUID FROM mutualfriends m WHERE m.CustUID2 = '9:2');

also takes a few ms....

Answer 1

There are any number of pages out on the net that will tell you how to optimize queries, but my first guess is that MySQL isn't using the index on mutualfriends because it's made of two columns.