Solved

MYSQL Query with subquery is horribly slow

Posted on 2011-09-13
5
446 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 108

Accepted Solution

by:
Ray Paseur earned 500 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 108

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now