Slow Queries

[trackpads]

Chuck;

Probably to do with the size of the gallery but I am going to attach some slow queries and see if you think there is a way to impove them. Like do I need to an an index and which one if you think that would help. Not speaking about the pp query itself, just my site.

# Time: 121102 22:26:26
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 2.110317 Lock_time: 0.000014 Rows_sent: 1 Rows_examined: 518043
use members_prime;
SET timestamp=1351909586;
SELECT exifinfo FROM pp_exif WHERE photoid=17868;

# Time: 121102 22:26:35
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 0.737828 Lock_time: 0.000026 Rows_sent: 37 Rows_examined: 815832
SET timestamp=1351909595;
SELECT id,user,userid,cat,storecat,title,ismature,bigimage,height,width FROM pp_photos WHERE approved=1 AND storecat=0 ORDER BY date DESC LIMIT 37;

# Time: 121102 22:26:37
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 2.002131 Lock_time: 0.000098 Rows_sent: 37 Rows_examined: 1631611
SET timestamp=1351909597;
SELECT id,user,userid,cat,storecat,title,ismature,bigimage,height,width, id*0+RAND() as rand_col FROM pp_photos WHERE approved=1 ORDER BY rand_col DESC LIMIT 37;

# Time: 121102 22:27:17
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 2.016908 Lock_time: 0.000053 Rows_sent: 1 Rows_examined: 518043
SET timestamp=1351909637;
SELECT exifinfo FROM pp_exif WHERE photoid=774451;

# Time: 121102 22:37:03
# User@Host: members_prime[members_prime] @ localhost []
# Query_time: 0.800466 Lock_time: 0.000041 Rows_sent: 52619 Rows_examined: 105238
SET timestamp=1351910223;
SELECT id,user,userid,cat,storecat,date,title,description,ismature,keywords,bigimage,width,height,filesize,views,medwidth,medheight,medsize,approved,rating,votes,allowprint,numcom,lastposter FROM pp_photos WHERE cat=716 AND approved < 2 ORDER BY disporder,lastpost DESC;

[trackpads]

Thanks again btw, its appreciated.

[trackpads]

Merged into post above

[Chuck S]

I will look and see if there is anything to comment on but yes you are probally right about it being a size issue. Like alot of the ones I glanced over your only grabbing one specific field entry or 37 entries so it is not like your scanning an entire table.

[trackpads]

Right but what does 'Rows_examined: 1631611' mean?

Can I add an index maybe to help it? I am sure the query is fine, just hoping I can find a work around.

[Chuck S]

There is no way to an some index as there are already the proper indexes.

The only other suggestion is try setting ROWS EXAMINED statement for certain things.

First say in showphoto.php for the exif info line 575 you could add say maybe the code in bold but test it

Content visible to verified customers only.

Then in pp-inc.php for your display_gallery function these statements add the code in bold

Content visible to verified customers only.

You can test that everything works fine and see the speed difference

[trackpads]

ok, let me try it

[Chuck S]

Not a problem let me know

[Chuck S]

Did you ever see if those queries we changed dissappeared from the slow queries log?

[trackpads]

I tried the first edit but got this:

Warning: Variable passed to each() is not an array or object in [path]/gallery/showphoto.php on line 596

And the second game me this: Parse error: syntax error, unexpected T_STRING in /home/members/public_html/gallery/pp-inc.php on line 1253

Could you post the files or pm me so I can upload them? I cant screw it up that way

[Chuck S]

This was just a suggestion not something I am applying to our code. You use at your own risk for reading on your thing see the internet

MySQL :: Rows Examined, why so many?

There is no error in our code or anything for us to fix. The example I posted might have been from an older post that may not be valid in later mysql

[trackpads]

Originally Posted by Chuck S

There is no error in our code or anything for us to fix.

Chuck, got it, didn't say otherwise. Are you not a morning person man?

thanks again,

-Jason

[Chuck S]

Nope just saying this was all just suggestions to try