CODEWITHSUNDEEP
mysqloptimization
mysqldude
mysqldude

Reputation: 23

hard time optimizing a query

I'm having a hard time getting better results on this query:

I have the following 2 tables:

DROP TABLE IF EXISTS `casino`.`mutualfriends`;
CREATE TABLE  `casino`.`mutualfriends` (
  `CustUID` varchar(64) CHARACTER SET ascii NOT NULL,
  `CustUID2` varchar(64) CHARACTER SET ascii NOT NULL,
  `FType` tinyint(4) NOT NULL,
  PRIMARY KEY (`CustUID`,`CustUID2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `casino`.`customers`;
CREATE TABLE  `casino`.`customers` (
  `CustFullName` varchar(45) NOT NULL,
  `CustEmail` varchar(128) NOT NULL,
  `CustUID` varchar(64) CHARACTER SET ascii NOT NULL,
  `CustMoney` bigint(20) NOT NULL DEFAULT '0',
  `SmallPicURL` varchar(120) CHARACTER SET ascii DEFAULT '',
  `LargePicURL` varchar(120) CHARACTER SET ascii DEFAULT '',
  PRIMARY KEY (`CustUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The customers table has 1M rows, mutual friends has 500K rows.

I'm having a hard time optimizing this query; it seems like there is a table scanning using it. I would like to minimize the scan:

SELECT c.CustUID AS Cuid, c.CustFullName, c.CustMoney, c.SmallPicURL
FROM `customers` c
WHERE c.`CustUID` = '1:1542073175'
UNION
SELECT m.`CustUID2` AS Cuid, c.CustFullName, c.CustMoney, c.SmallPicURL
FROM `mutualfriends` m, `customers` c
WHERE m.`CustUID` = '1:1542073175'
AND c.`CustUID` = m.`CustUID2`
UNION
SELECT m.`CustUID` AS Cuid, c.CustFullName, c.CustMoney, c.SmallPicURL
FROM `mutualfriends` m, customers c
WHERE m.`CustUID2` = '1:1542073175'
AND c.CustUID = m.`CustUID`

Upvotes: 2

Views: 125

Answers (3)

Martin Schapendonk
Martin Schapendonk

Reputation: 13536

Try it with this query:

select *
from customers
where CustUID in (
  select CustUID
  from mutual_friends
  where CustUID2 = '1:1542073175'
  union all
  select CustUID2
  from mutual_friends
  where CustUID = '1:1542073175'
  union all
  select '1:1542073175'
)

Is that any faster?

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

I think you need an index on MutualFriends.CustUID2 (a duplicates index, not a unique index; and not as part of the primary key). The primary key probably gives you a usable index for the queries where the identified customer ('1:1542073175') is listed first.

Check the explanation of the query plan.

I think you're right that there is a sequential scan for the third part of the UNION, and possibly the second too.

Upvotes: 2

Treby
Treby

Reputation: 1320

try this query

SELECT c.CustUID AS Cuid, c.CustFullName, c.CustMoney, c.SmallPicURL
FROM `customers` c INNER JOIN `mutualfriends` m
IN c.CustUID = m.`CustUID`
WHERE m.`CustUID2` = '1:1542073175'

Upvotes: 0

Related Questions