Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MYSQL Query with subquery is horribly slow

Posted on 2011-09-13
5
Medium Priority
?
492 Views
Last Modified: 2012-05-12
In my database I have three tables in play for this scenario:

"things" contains about 13,000 rows of things including these columns:
- thingid
- ownerid (reference to who owns that thing in the users table)
- name (name of thing)
- created (datetime for when the thing was made)

"users" contains about 65,000 rows for users including:
- id (id of user)
- screenname (nickname for user)

"collections" contains about 840,000 rows for "things" that have been collected
- ownerid (who owns this collected thing - DIFFERENT than who owns the thing)
- thingid (reference to entry in things table)

"wishlist" contains about 9,000 rows for wishlist items.  These are lists of things people want but do not have.
- thingid

This may get convoluted, but let's take a use case:

- You sign up on the site as user #1 with username "joe"
- You create a "thing" which gets one single entry in the thing table with thingid T1
- You start giving these things away (which you have many of)

- I am user #2
- You gave me one of your things
- I added an entry for your thing to my collection
- This created a row in the "collections" table with ownerid = 2 and thingid = T1

- User #3 wants the thing made by user #1 but doesn't have one
- User #3 creates a wishlist entry
- This creates a row in the wishlist table for thingid T1

Now, as user #2 - I want to view my collection.  I also want to see if someone had one of my items on their wishlist.  

That is what the query below is intended to do.  It selects all the items from my collection (remember, I'm user #2) and grabs the thing name, thing created, and user screen name of the thing's owner (that's you, "joe")

I also want to know for each item in user #2's collection, is there at least one entry for it in the wishlist table.  So I'm doing a subquery.

WITH the subquery, it's taking 30+ seconds to return this.

If I take out the subquery from the select, there is no measurable difference.  

If I take out the subquery from the "where" statement, it runs about 16 seconds

If I take out both subqueries it runs in a fraction of a second.

How can I accomplish what I am trying to do most effectively?  Is there some kind of join that would work better?  I tried doing something like "where InAWishlist > 0" in the where clause, attempting to use the results of the first subquery like a column test but I get an unknown column error.

SELECT 
	c.*, t.ownerid, t.name, t.created, u.screenname,
	IF((SELECT COUNT(*) FROM wishlist AS W WHERE W.thingid=c.thingid)>0,1,0) AS InAWishlist 
FROM 
	users u, collections c, things t 
WHERE 
	c.ownerid = '2' AND 
	(SELECT COUNT(*) FROM wishlist AS W WHERE W.thingid=c.thingid) > 0 AND 
	t.id = c.thingid AND 
	u.id = t.ownerid 

Open in new window

0
Comment
Question by:trippy1976
5 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 36529581
Avoid SELECT * for starters.  SELECT only the columns you actually need.

Then use EXPLAIN SELECT to see what the engine is doing with that query.  Make sure you have indexes on every column used in WHERE, JOIN, ORDER or GROUP.

Make those changes and try it again, then post back if it is still too slow.
0
 
LVL 5

Expert Comment

by:DerZauberer
ID: 36529656
Dunno if that's same for mysql, but in other sql it's better to use something like

EXISTS (SELECT 1 as dummy FROM wl AS W WHERE W.id=c.id)

Open in new window


instead of
(SELECT COUNT(*) ... )>0

Open in new window

as it doesn't have to count all rows, but
stops on first finding.
0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 36529699
One other thing you can do is eliminate the If statement in your query because it will always evaluate to a 1 because of the select count(*).... condition you put on the query in the where clause.

And, as Ray said, create indexes on all of the id columns you are using in your where clause.

I had a query one time that "never" completed (I couldn't wait long enough) that completed in less than a second once I applied the proper indexes.
0
 
LVL 4

Author Closing Comment

by:trippy1976
ID: 36530123
Ray, you seem to post everywhere and always have great info.

At first I was like "c'mon guys, give me some credit, of COURSE I have indexes on all my tables."

Hrm.  Well, I thought I did.  I added indexes for all the ID stuff and holy camoly.  That certainly addresses the performance issue to my full and complete satisfaction.

I'll study the other ideas here too, I'm sure they would help but the query is now under one second.  That's crazy.

Many thanks to all
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 36530237
Yeah, I've missed an index before, so I recognize the symptoms!  I'm not really everywhere - I only look at questions in the PHP Zone and a few others when I get a Neglected Question Alert.

Thanks for the points and for your kind words, ~Ray

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month20 days, 21 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question