Mysql SubQuery Optimization

Question

I have a users table with 450,000 records. The table structure is as so:

  UserID Int(11) Primary Key
  Firstname (50) Varchar
  Lastname  (50) Varchar

I also need to check two other table to see if the UserID is in those tables. (the structure is a bit different, but the table have the same UserID) I am running this sub query below, and is running very slow. Appreciate a second set of eyes to offer up a fresh perspective to help run a bit faster...

SELECT 
`Users`.`Firstname`, 
`Users`.`Lastname`, 
`Users`.`UserID` 
FROM `Users`
WHERE `Users`.`UserID` IN  (SELECT `admin`.`UserID` FROM `admin` WHERE `admin`.`UserID`=`User`.`UserID`)
AND `Users`.`UserID` IN  (SELECT `elite`.`UserID` FROM `elite` WHERE `elite`.`UserID`=`Users`.`UserID`)
AND `Users`.`Lastname` LIKE '%smith%'

What indexes do you have on the tables? Post SHOW CREATE TABLE ... for each table, and also post EXPLAIN SELECT ... output. — Mark Byers
– Mark Byers, Commented Apr 9, 2012 at 13:58

Madan · Accepted Answer · 2012-04-09 14:01:11Z

2

i guess instead of sub queries you should go with inner joins...because right now you are fetching the user id from the admin table, elite table and checking the same must be in user table...so go ahead with join and see the difference.

answered Apr 9, 2012 at 14:01

Madan

6905 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Byers · Accepted Answer · 2012-04-09 14:10:47Z

1

The absolutely most important thing when optimizing queries in MySQL is to ensure you have the appropriate indexes. Check what indexes you have (using SHOW INDEX), and what indexes the query is using (using EXPLAIN SELECT). Indexes are absolutely vital for good performance once your tables get large.

In particular you should make sure that you have an index on the UserID column in all three tables. I'd also suggest adding a foreign key contraint if that is appropriate in your situation.

However if you have already checked you have the correct indexes and you still aren't getting appropriate performance, you can try using joins instead instead of subselects:

SELECT 
    Users.Firstname, 
    Users.Lastname, 
    Users.UserID 
FROM Users
JOIN admin USING (UserID)
JOIN elite USING (UserID)
WHERE Users.Lastname LIKE '%smith%'

Note that LIKE '%smith%' is unable to use an index efficiently. You should avoid LIKE expressions that start with a wildcard if possible.

edited Apr 9, 2012 at 14:10

answered Apr 9, 2012 at 14:00

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

1 Comment

user1233508 Over a year ago

It's worth mentioning that you can't put an index on '%smith%'.

Rory Hunter · Accepted Answer · 2012-04-09 14:00:17Z

1

Start with joins;

SELECT 
`Users`.`Firstname`, 
`Users`.`Lastname`, 
`Users`.`UserID` 
FROM `Users`
INNER JOIN `admin` ON (`Users`.`UserID` = `admin`.`Users`)
INNER JOIN `elite` ON (`Users`.`UserID` = `elite`.`UserID`)
WHERE `Users`.`Lastname` LIKE '%smith%'

Then check your indices.

You should also be away that the your last name search will be slow, because it is not anchored at the start or the end of the Lastname string.

answered Apr 9, 2012 at 14:00

Rory Hunter

3,5001 gold badge16 silver badges16 bronze badges

Collectives™ on Stack Overflow

Mysql SubQuery Optimization

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related