optimizing query - 10 secs+ to load

echo_josh

Hi,
I'm having a problem with this query. The tables in questions, all together, have about 40k registers, and the query is taking a lot to load (Even on a dual xeon 3.0).
So, is there anything I could do to make it faster? Maybe index tables would help too (what fields in that case).

SELECT users.login, users.nickname, users.city, users.uf, fotos.ext, fotos.userID, max(fotos.fotoID), fotos.ext, users.userID FROM fotos, flogs, users WHERE fotos.userID = flogs.userID && fotos.userID = users.userID GROUP BY fotos.userID ORDER BY 7 DESC LIMIT 0,9

thank you very much

bubblenut

Try using explain to see what's goin on with the query. If that doesn't show up anything obvious then do a desc and a show indexes on (or maybe from, I can't remember) on the tables involved and pot up the results here.

echo_josh

EXPLAIN:

table type possible_keys key key_len ref rows Extra
flogs ALL NULL NULL NULL NULL 4724 Using temporary; Using filesort
fotos ALL NULL NULL NULL NULL 30738 Using where
users eq_ref PRIMARY PRIMARY 4 fotos.userID 1

and here's a desc for fotos, the table that is probably causing the delay:

fotoID int(10) PRI NULL auto_increment
userID int(10) 0
time int(20) 0
title varchar(100)
text text
ext varchar(5)
allowComments int(1) 0

thanks again

Drakla

get an index on fotos.userID as quick as possible.

And can I ask why you're ordering by 7

echo_josh

just indexed it, do you know how much time does it take for me to see some difference?

I'm ordering by 7 because its max(fotos.userID), and if I tried using this on ORDER clausule it would crash.
So I just used the field position.

Thanks, anything else you could possibly do? Maybe on the query itself?

echo_josh

WOW, just saw the difference. 1-2 secs.
cant thank you enough 🙂
do you think I should index other tables.userID too?

thanks again, that really help me out!

Drakla

If they're used in equivalence tests, and the number of selects outweighs the number of inserts, and they're not already primary keys (which I would assume users.userid is) then index away. It's quite easy to get queries that take up to an hour down to a few thousanths of a second with proper indexing.

Also do you mean to you && and not AND in the query? They're logically the same, but using AND is more usual.

That ordering by 7 is still very very confusing.

echo_josh

&& equals to AND, yes, but I use && in PHP too, so its more simple for me.

the order by 7, look:

SELECT field1, field2, field3 FROM table ORDER BY 2

2 equals to field2 in this case.. its no big deal, even phpmyadmin was crashing if I were not using this.

Drakla

Oh yeah, you can specify a position in order by, that's a new one on me.

echo_josh

ok, another one for you guys.
Would this query:

UPDATE stats SET hits = hits + 1

overload my server? I know its no big deal, but its tens of thousands of hits per day, what do you think?
Anything I could do on table stats also?

Drakla

How many columns and fields are in the stats table? Not many I imagine.

Tens of thousands of hits a day isn't going to impact on your server in the slightest. The main overhead will be connecting with the mysql server each time, and if you'll be doing that in the script anyway to deliver other stats / db content then it really is absolutely trivial.

I've just run a benchmark doing five hundred million updates of that kind on a table with 12 fields, and it took just over 5 seconds.

SELECT BENCHMARK(500000000, 'UPDATE table SET hits = hits + 1');

echo_josh

I'm more afraid about the overhead, and yes, the table has just few columns.
I guess I'll try it out for a while to see how it turns out.

thank you all again for the great help, I really appreciate that 🙂

btw, nice signature Drakla