Sorry if this is in the wrong place - please move if necessary - I am making a plugin for my forum, and I am a bit worried about my query - I don't want to slow the database too much... Can someone take a look and advise where (if needed) I can improve it? This is my query (theres a lot more plugin of course (templates etc), but this is the bit I would like some help on).
$mdbquery = $db->query("
SELECT p.*, t.*, t.subject AS threadsubject, u.username, u.usergroup, u.displaygroup, SUM(a.downloads) AS downloads, f.*, i.*, i.name AS iconname,
f.name AS forumname
FROM (".TABLE_PREFIX."posts p
INNER JOIN ".TABLE_PREFIX."attachments a ON (a.pid=p.pid))
LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=p.fid)
LEFT JOIN ".TABLE_PREFIX."threads t ON (t.tid=p.tid)
LEFT JOIN ".TABLE_PREFIX."icons i ON (i.iid=t.icon)
LEFT JOIN ".TABLE_PREFIX."users u ON (t.lastposter=u.username)
WHERE p.uid = '".$mybb->user['uid']."'
AND p.visible = '1'
AND a.filetype IN('".implode("','", $filetype_tid_array)."')
GROUP BY t.tid
ORDER BY a.downloads DESC
");
Its to get a table of the uploaded attachments and the number of downloads, to show in the usercp, and the filetype bit is for an textbox in the settings where you can choose what filetypes you want to use.
The table in the UCP will have the thread icon, thread title, number of DLs, views and comments. There is also a totals box at the bottom.
you do not need to pull every field (the x.* part) for every table. Even if it is required in a join, you do not need to output the fields for that table. you should specify only the fields you need the data from
as you have it, you will end up with like 65+ fields being returned
Also, if you are going to list every attachment the user has posted, there is no need to aggregate and generate the sum of the downloads as each attachment downloads field is a running total. if you are aggregating to get the total, that wont happen since you are getting output for reach record, the "downloads" field you are trying to output will just match the downloads field in the a.* results.
You should skip that and sum the total downloads as you process the query results and output that value as your total once you have run through the query results
ignore that middle bit, i missed that you were not including the attachments individually, so the aggregate function is fine
the last two lines of your post are a bit confusing, first you mention "table of the uploaded attachments and the number of downloads" which implies a list of individual attachments (at least to me) and then you state "table in the UCP will have the thread icon, thread title, number of DLs, views and comments" whcih does not mention attachments so I assume a summary output.
Your query does the summary output fine but its clunky outputting all that data. on my dedicated server the query for a single user (me) and a single image type (jpeg) runs in 0.2-0.3s and that is with 1.1M posts
Thank you Pavemen. I will work on that - thank you
This is a picture of what my plugin will be - see attachment. I think you are right though - the point is to count the number of downloads of each attachment, so the roll up is not needed. If it was the number of downloads per thread - then yes, I would need to roll it up.
The total i spoke of was the total of ALL downloads/comments/views. That is the summary at the bottom.
given the image you showed, the query overall will work, but it is kind of slow. definitely limit the column being returned and then put a LIMIT 10 or whatever to reduce the number of result records.
the roll up is needed given the image you showed since they are summaries, but if you are intending on outputting multiple records for a single thread (if there are more than one upload per thread per user) then you need to output the attachment info such as local filename and get rid of the roll up.
Then run a separate query to get the total attachments (direct count(*) query on attachments table via UID) which will be fast
still confused as to what you really want output int he profile
do you want summary of all attachments from a user within a thread and show the last 10 threads that the user has uploaded files to? example, last 10 threads in which user has posted any number of attachments
or
do you want to list the last 10 attachments from the user regardless of what threads they are in or how many? example, 10 records listing the each of the 10 attachments and the threads they are posted in, regardless of how many threads that may be
Hi Pavemen - its not for profile - its for user control panel. We already have a list of ALL attachments from users - in the manage attachments. This is for certain file types in certain forums.
Some sites like to encourage members to upload items for others to share - but its not always interesting for the uploaders to see stats on things like the pictures they uploaded, or a quick attachment of some code snippets etc.
This list will allow the admin to set the forums and filetypes which are called used in the query. Its per thread, at the moment, so that is what the rollup was for.
It would be good to have a limit - like top ten - and then an option to roll out them all if wanted (per user). The list is for users to see what interest there is in the files they upload and encourage them to share more to get MORE stats.
i get it now. dont forget to consider pagination in your results, so you can limit the number of records in the tables.