...

View Full Version : Daft question.. feel stupid asking it ;)



tangoforce
03-09-2013, 01:26 AM
Ok, I'm running this query:


select *, (select count(*) from mail_emails as emails where user_id = '110' and `read` = '0' and emails.folder_id = folders.folder_id) as unread, (select count(*) from mail_emails as emails where user_id = '110' and `read` = '1' and folder_id = folders.folder_id) as `read`, (select count(*) from mail_emails as emails where user_id = '110' and folder_id = folders.folder_id) as `total` from mail_folders as folders join mail_folder_names as names on folders.folder_name_id = names.folder_name_id join mail_folder_types as types on types.type_id = folders.type_id where folders.user_id = '110' order by sys_type asc, folder asc, types.type_id asc



Now obviously in my mysql log, that all shows up. What I'd like to know is, does that all count as seperate queries or just one large query? :confused:

LearningCoder
03-09-2013, 09:22 AM
Hi dude,

Not sure if you have come across this but I bookmarked this the other night because I've used one in my current site.

http://dev.mysql.com/tech-resources/articles/subqueries_part_1.html

Probably more of a Fou question.:D

Regards,

LC.

Fou-Lu
03-09-2013, 01:46 PM
Not a Fou question, that would be an old pedant question :D
I would expect that each one needs to evaluate. I think that dbms' in general can do some cache work on constant data or non-variable subqueries, but these all have a reliance on the record itself on the mail_emails.folder_id = mail_folders.folder_id.

tangoforce
03-09-2013, 08:38 PM
Hmm so in other words I need to somehow clean that up too... humbug!

Old Pedant
03-11-2013, 12:10 AM
First of all, let's make that *READABLE*:


select *,
(select count(*)
from mail_emails as emails
where user_id = '110' and `read` = '0'
and emails.folder_id = folders.folder_id) as unread,
(select count(*)
from mail_emails as emails
where user_id = '110' and `read` = '1'
and folder_id = folders.folder_id) as `read`,
(select count(*) from mail_emails
as emails where user_id = '110'
and folder_id = folders.folder_id) as `total`
from mail_folders as folders join mail_folder_names as names
on folders.folder_name_id = names.folder_name_id
join mail_folder_types as types on types.type_id = folders.type_id
where folders.user_id = '110'
order by sys_type asc, folder asc, types.type_id asc

Okay, you have, essentially, 4 separate queries there.

You also are doing a really really bad thing by starting with SELECT *.

*SURELY* you don't need (just to pick one example) to get both types.type_id and folders.type_id when they are identical. (In fact, getting both can cause problems in your PHP code.) And of course there is no reason at all to get user_id when you have explicitly limited it to just user_id='110' (which, by the by, is another error: you should not put numbers in apostrophes if the field you are comparing to is a numeric field--it works because MySQL sloppily allows it, but it's a very bad habit to get into).

ANYWAY...you could easily chop that down to TWO queries, thus:


SELECT explicit, list, of, needed, fields, only, X.total, X.read, X.unread
FROM ( SELECT COUNT(*) AS total,
SUM(read) AS read,
-SUM(1-read) AS unread
FROM mail_emails WHERE user_id = 110 ) AS X,
mail_folders AS folders,
mail_folder_names AS names,
mail_folder_types AS types
WHERE folders.folder_name_id = names.folder_name_id
AND types.type_id = folders.type_id
AND folders.user_id = 110
ORDER BY sys_type ASC, folder ASC, types.type_id ASC

Something like that, at least.

That code depends on the value of mail_emails.read being *ONLY* 1 or 0, of course.

tangoforce
03-11-2013, 06:25 PM
old pedant, you're a legend :thumbsup: I'll give that a whirl and see what I can do with it :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum