|
|
Posted by cgmckeever on 09/30/37 11:48
I am trying to figure out what is more efficient, a single query with a
JOINED nested query or
two separated queries. Each of these only produce one record of data,
however the nested query produces _many_ records and relies on the JOIN
to pear it down. So I am thinking that the two separate queries are
much more efficient. Below are the examples
SELECT user.key, search_count.daily_counter,
search_count.historical_counter, user.id,
search_count.counter, user.date_created,
user.timeoffset, allow_domain.domain_count
FROM user
JOIN search_count ON user.id=search_count.user_id
LEFT JOIN (SELECT count(*) AS domain_count, max(user_id) AS
max_user_id
FROM allow_domain GROUP BY user_id)
AS allow_domain ON user.id=allow_domain.max_user_id
WHERE hash_key = '{$escaped_pkey}'";
note: this is the query that produces many results =
"SELECT count(*) AS domain_count, max(user_id) AS max_user_id FROM
allow_domain GROUP BY user_id"
or two queries
SELECT user.key, search_count.daily_counter,
search_count.historical_counter, user.id,
search_count.counter, user.date_created,
user.timeoffset
FROM user
JOIN search_count ON user.id=search_count.user_id
WHERE hash_key = '{$escaped_pkey}'
SELECT count(*) AS domain_count, max(user_id) AS max_user_id
FROM allow_domain
WHERE user_id = {$user_id}
note: second query uses the user_id obtained from the first query.
Thanks -
Chris
Navigation:
[Reply to this message]
|