PhotoPlog

PhotoPlog (http://www.photoplog.com/forum/index.php)
-   Troubleshooting & Problems (http://www.photoplog.com/forum/forumdisplay.php?f=6)
-   -   SQL Query issue (http://www.photoplog.com/forum/showthread.php?t=3121)

j3st3r 01-09-2010 03:54 PM

SQL Query issue
 
Well since I cant seem to post in the pro section I will try here...

I have PhotoPlog Pro v.2.1.4.8 and received and email from my hosting company stating:

We have disabled your database to return the server to normal usage. To re-enable your database, you will need to correct the following query:

SELECT f1.userid, f1.fileid, f1.filename, f1.title
FROM photoplog_fileuploads AS f1
LEFT JOIN photoplog_fileuploads AS f2
ON (f2.userid = f1.userid AND f2.fileid > f1.fileid)
WHERE 1=1

AND f2.fileid IS NULL
AND f1.userid IN
(1,2,3,4,6,221,8,9,21,14,10,11,224,13,15,16,17,18, 20,22,23,25,235,238,31,86,89,99,
125,113,249,120,123,259,117,118,122,128,129,130,13 8,141,253,150,149,263,154,157,258,159,243,173,
171,242,167,166,239,209,206,241,210,203,244,214,20 8,211,213,218,216,217,230,228)
AND f1.catid NOT IN (0)
AND f1.moderate = 0

This query examines 2579099 rows, which is unacceptable in shared hosting.

How do I go about correcting this issue....or where do I go to find this query

Morgan 01-10-2010 05:40 PM

The query is in the PhotoPlog memberlist.php file. It is hard to think that the query is examining over 2.57+ million rows. Run this query and it'll tell you how many rows are being examined:
Code:

EXPLAIN SELECT f1.userid, f1.fileid, f1.filename, f1.title
FROM photoplog_fileuploads AS f1
LEFT JOIN photoplog_fileuploads AS f2
ON (f2.userid = f1.userid AND f2.fileid > f1.fileid)
WHERE 1=1
AND f2.fileid IS NULL
AND f1.userid IN
(1,2,3,4,6,221,8,9,21,14,10,11,224,13,15,16,17,18,20,22,23,25,235,238,31,86,89,99,
125,113,249,120,123,259,117,118,122,128,129,130,138,141,253,150,149,263,154,157,258,159,243,173,
171,242,167,166,239,209,206,241,210,203,244,214,208,211,213,218,216,217,230,228)
AND f1.catid NOT IN (0)
AND f1.moderate = 0;

Please report back the full results of the above query.

j3st3r 01-11-2010 03:40 AM

http://i77.photobucket.com/albums/j5...e/Misc/SQL.jpg

Morgan 01-11-2010 09:26 PM

Thanks, but that looks like the output from the query itself. The query you need to run is this one:
Code:

EXPLAIN SELECT f1.userid, f1.fileid, f1.filename, f1.title
FROM photoplog_fileuploads AS f1
LEFT JOIN photoplog_fileuploads AS f2
ON (f2.userid = f1.userid AND f2.fileid > f1.fileid)
WHERE 1=1
AND f2.fileid IS NULL
AND f1.userid IN
(1,2,3,4,6,221,8,9,21,14,10,11,224,13,15,16,17,18,20,22,23,25,235,238,31,86,89,99,
125,113,249,120,123,259,117,118,122,128,129,130,138,141,253,150,149,263,154,157,258,159,243,173,
171,242,167,166,239,209,206,241,210,203,244,214,208,211,213,218,216,217,230,228)
AND f1.catid NOT IN (0)
AND f1.moderate = 0;

It should produce a table with the following results:
Code:

id        select_type        table        type        possible_keys        key        key_len        ref        rows        Extra

j3st3r 01-12-2010 02:54 AM

http://i77.photobucket.com/albums/j5...QL_Explain.jpg

Morgan 01-15-2010 02:58 AM

In the rows column you have 3407 plus 757 for a total of 4164 rows.
Quote:

Originally Posted by mysql.com
The rows column indicates the number of rows MySQL believes it must examine to execute the query.

So it would seem that the query is not examining 2579099 rows, but you can undo the PhotoPlog memberlist stuff if you want to be rid of the query anyway.


All times are GMT. The time now is 03:42 PM.

Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.