help optimize query of three tables

greg890

Hello,

The query I wrote is horrible and is beyond the resources I have on my server.

How can I optimize this? I'm not so concerned about the order by rand() part as I am getting the data from the third table, because that is the part that is taking all the resources.

Here's what I have:

$sql = "select * from fshop_products, music_musician join 
fshop_product_cat_mp3 on fshop_products.cat_id=fshop_product_cat_mp3.cat_id 
where is_product_sale=1 and fshop_product_cat_mp3.cat_status='Active' and 
fshop_products.uploaded_song_userid = music_musician.musician_userid and 
music_musician.paid_member = 1 order by rand() limit 0,15";

I tried writing a subquery, but couldn't get it to work any better. Thanks for any help.

Greg

laserlight

What is the relevant database schema, and what exactly are you trying to select?

greg890

fshop_products has the price, image, title, and userid
fshop_product_cat_mp3 has the genre
music_musician let's me know if they are an upgraded member with the userid

So I'm using a loop to get 15 random results where that pulls the image, price, title, userid, and member status (upgrade or free).

laserlight

How are the tables related? What are the primary and foreign keys? What columns do you want to select, and why?

nemonoman

Get, review, and share the results of an EXPLAIN of your query:
Simply put EXPLAIN in front of the query:

EXPLAIN
select * from fshop_products, music_musician join
fshop_product_cat_mp3 on fshop_products.cat_id=fshop_product_cat_mp3.cat_id
where is_product_sale=1 and fshop_product_cat_mp3.cat_status='Active' and
fshop_products.uploaded_song_userid = music_musician.musician_userid and
music_musician.paid_member = 1 order by rand() limit 0,15

My guess is that you are doing full-table scans, and that you need to index some of these columns

greg890

Thanks,

I had an unkown field in my query, but that wasn't actually the problem - just a typo when I posted the message.
(uploaded_song_userid should be uploaded_songid)

I "x'd" one of the fields I'd like to keep private.

The EXPLAIN results are:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE fshop_products ALL cat_id NULL NULL NULL 110442 Using where; Using temporary; Using filesort
1 SIMPLE music_musician ALL PRIMARY NULL NULL NULL 6926 Range checked for each record (index map: 0x1)
1 SIMPLE fshop_product_cat_mp3 eq_ref PRIMARY PRIMARY 4 xxxxxxxxx.fshop_products.cat_id 1 Using where

Sxooter

select * from fshop_products, music_musician join
fshop_product_cat_mp3 on fshop_products.cat_id=fshop_product_cat_mp3.cat_id
where is_product_sale=1 and fshop_product_cat_mp3.cat_status='Active' and
fshop_products.uploaded_song_userid = music_musician.musician_userid and
music_musician.paid_member = 1 order by rand() limit 0,15";

You are mixing two join methods, which is bad form and can cause poor query planning.

Stick to join syntax (also learn to indent your SQL so it's easier to understand / troubleshoot):

select * 
from fshop_products
join music_musician 
    on (fshop_products.uploaded_song_userid = music_musician.musician_userid)
join fshop_product_cat_mp3 
    on (fshop_products.cat_id=fshop_product_cat_mp3.cat_id)
where 
    is_product_sale=1 
    and fshop_product_cat_mp3.cat_status='Active'
    and music_musician.paid_member = 1 
order by rand() limit 0,15";

If you have a large data set then order by rand() is going to be slower at a geometric rate, and get super slow once the dataset you're running it on exceeds buffer memory in your db. There's a sticky post in this forum on ways around that, but first, let's get this query working and readable so I'm not squinting when I look at it. 🙂

Note that you should time the speed of this query first:

select count(*) 
from fshop_products
join music_musician 
    on (fshop_products.uploaded_song_userid = music_musician.musician_userid)
join fshop_product_cat_mp3 
    on (fshop_products.cat_id=fshop_product_cat_mp3.cat_id)
where 
    is_product_sale=1 
    and fshop_product_cat_mp3.cat_status='Active'
    and music_musician.paid_member = 1 ;

to see how long it's taking to just return the whole set. the order by rand() is going to take a bit more time on its own after that. If the time to retrieve just the count(*) is long, don't expect any miracles using rand() to popup, we'll need to come up with a way to apply one of the methods mentioned in the stick thread to speed things up.

nemonoman

You need to index fshop_products.cat_id
You need to index music_musician.paid_member
You need to index music_musician.user_id

You appear also be forcing extra reads by using NULL vs NOT NULL data attribute for those columns, particularly if you are attempting a JOIN on those columns. Change the attribute to NOT NULL BEFORE indexing

That will help. Index the columns and run EXPLAIN again. I think there's one other speedup to be gained, but I need to see the EXPLAIN after you alter these tables.

I am a big believer that NOT NULL is ALWAYS preferable for speed, but when attempting a JOIN on a regularly used query, NOT NULL is the ONLY practical attribute,.

Additionally, typically no business reason to allow columns with information like "catalog ID" or "paid member" to be NULLable.

greg890

Sxooter wrote:
You are mixing two join methods, which is bad form and can cause poor query planning.

Stick to join syntax (also learn to indent your SQL so it's easier to understand / troubleshoot):

...

If you have a large data set then order by rand() is going to be slower at a geometric rate, and get super slow once the dataset you're running it on exceeds buffer memory in your db. There's a sticky post in this forum on ways around that, but first, let's get this query working and readable so I'm not squinting when I look at it. 🙂

Note that you should time the speed of this query first:

...

to see how long it's taking to just return the whole set. the order by rand() is going to take a bit more time on its own after that. If the time to retrieve just the count(*) is long, don't expect any miracles using rand() to popup, we'll need to come up with a way to apply one of the methods mentioned in the stick thread to speed things up.

Thanks for the rewrite and explanation. Unfortuantely, I can't clock it, because the database is too large, mysql cpu starts spiking at 100%, and after a minute or so there is no result, so I end up restarting mysql.

I have a very large database, and some of these tables have well over 100,000 rows.

I tried running it without the rand(), and still timed out.

select * 
from fshop_products
join music_musician 
    on (fshop_products.uploaded_song_id = music_musician.musician_userid)
join fshop_product_cat_mp3 
    on (fshop_products.cat_id=fshop_product_cat_mp3.cat_id)
where 
    is_product_sale=1 
    and fshop_product_cat_mp3.cat_status='Active'
    and music_musician.paid_member = 1 
limit 0,15 ;

I will give a try to the other suggestion regarding NOT NULL, but I need to look over the PHP code before hand, as some of the queries previously set up may be affected.

Sxooter

How many rows are there in your various tables?

1 minute, by the way, is not very long. If it was taking 30 minutes I'd worry.

Let it run and see how long it takes.

nemonoman

The problem is the 2 full table scans required to make the join happen=

110442 * 6926 records scanned.

I agree that 1 minute is not too long to wait for an ad-hoc query, but if this is a production method, NOT NULL and INDEX the columns I noted above. Simply making the NOT NULL will produce marginal improvements. Indexing them should bring this query into managable state.

Sxooter

How selective is this where clause?

specifically these bits:
is_product_sale=1
fshop_product_cat_mp3.cat_status='Active'
music_musician.paid_member = 1

Those parts are likley selective enough that an index scan would help. OTOH, if most cat_status='Active', is_product_sale=1 or paid_member=1 are true, then an index won't help that much, because you're still going to be retrieving most of the rows on the respective tables.

OTOH, how selective is this:

fshop_products.uploaded_song_userid = music_musician.musician_userid

Because that might actually help if it's very selective, then an index on those fields would be helpful.

My point here is that indexes aren't magic. They only really help on highly selective fields.

So, tell us what you're trying to do, and maybe there's a better approach than how you're doing it.

Looks to me like you're looking for a random selection from most of your data, assuming most of these records are going to answer yes to your where clause.

If that's the case, you're gonna need to do some work on pre-building summary data somewhere that lets you walk through the data set 15 at a time, pre-randomized.