Large Rowscans - Can this be improved?

pgowder · September 21st, 2007, 09:23 AM

I'm trying to do some tuning to MySQL to help with performance.

I saw this in my slow query log:

Code:

Content visible to verified customers only.

Can this be improved?

Chuck S · September 23rd, 2007, 08:54 AM

That is not a large rowscan. That should a very explicit result as thats what the query is asking for a specific record, there is no pulling of unneeded information.

pgowder · September 23rd, 2007, 08:56 AM

Quote:

Originally Posted by Chuck S

That is not a large rowscan. That should a very explicit result as thats what the query is asking for a specific record, there is no pulling of unneeded information.

Then why did it examine 17k rows to get the result?

Chuck S · September 23rd, 2007, 09:01 AM

I am just telling you the query is not this

SELECT exifinfo FROM exif

the query is this and we only are pulling information for one entry.

SELECT exifinfo FROM exif WHERE photoid=19367

If you enter this query in phpmyadmin the results you get are ONE ENTRY. There is no way to optimize that query it is already the best it can every be.

I am not a mysql wizard here but issuing a direct specific query to pull one row of information is about as good as your gonna get. Sometimes depending on the query doing JOINED queries as you see in some portions of the script as we do can save time. In this case the query called is done because you are electing to show exif information on a photo.

pgowder · September 23rd, 2007, 09:05 AM

Quote:

Originally Posted by Chuck S

I am just telling you the query is not this

SELECT exifinfo FROM exif

the query is this and we only are pulling information for one entry.

SELECT exifinfo FROM exif WHERE photoid=19367

If you enter this query in phpmyadmin the results you get are ONE ENTRY. There is no way to optimize that query it is already the best it can every be.

I am not a mysql wizard here but issuing a direct specific query to pull one row of information is about as good as your gonna get. Sometimes depending on the query doing JOINED queries as you see in some portions of the script as we do can save time. In this case the query called is done because you are electing to show exif information on a photo.

It appears that this happens when there is no exif information for a photo.

So you don't have logic in there for when there is no exif. It just scans the entire table looking for it. You need to have some logic to prevent the rowscan when there isn't exif information.

Chuck S · September 23rd, 2007, 09:14 AM

Not many things we can do but I will pass it along although incase something comes to mind.

pgowder · September 23rd, 2007, 09:40 AM

Quote:

Originally Posted by Chuck S

Not many things we can do but I will pass it along although incase something comes to mind.

Thanks! In my slow query log this is showing up hundreds of times. It's doing it every time a user views any photo that doesn't have exif. Which for me is 1000's.

Is there a way to have exif show only when requested?

Chuck S · September 23rd, 2007, 10:04 AM

How is the query timing though this is not really an issue that I see. If I view query timing it is non existant on my install. In pp-inc.php set $showsqldebug to 2 and post a link so we can see timimg.

pgowder · September 23rd, 2007, 10:09 AM

Quote:

Originally Posted by Chuck S

How is the query timing though this is not really an issue that I see. If I view query timing it is non existant on my install. In pp-inc.php set $showsqldebug to 2 and post a link so we can see timimg.

You can see it here:
http://www.powwows.com/galleries/sho...=17439&cat=506

But right now there is no load. I'm going to turn off debug in a minute and I'll revisit it tomorrow during heavy load.

Chuck S · September 23rd, 2007, 10:17 AM

Ok well you did not turn on query timing as instructed but anyway thats what I would initially look at. If any of the queries are returning slow results which hang a page.

pgowder · September 23rd, 2007, 10:18 AM

Quote:

Originally Posted by Chuck S

Ok well you did not turn on query timing as instructed but anyway thats what I would initially look at. If any of the queries are returning slow results which hang a page.

I did turn it on. And it shows a low number. But nobody is on the site right now.

What matters is how this affects other things during full load.

pgowder · September 23rd, 2007, 10:18 AM

Quote:

Originally Posted by Chuck S

Ok well you did not turn on query timing as instructed but anyway thats what I would initially look at. If any of the queries are returning slow results which hang a page.

And I know that this is a slow query because it is showing up in the slow query log!