help merging 2 simple queries

[maltrecho]

SELECT COUNT(*) AS first_count
FROM this_table
WHERE (first_field_user_id = '999')

SELECT COUNT(*) AS second_count
FROM this_table
WHERE (second_field_user_id = '999')

How can I merge this two queries? :confused:

[vinyl-junkie]

Very easily, like so:

(SELECT COUNT(*) AS first_count
FROM this_table
WHERE (first_field_user_id = '999'))
UNION
(SELECT COUNT(*) AS second_count
FROM this_table
WHERE (second_field_user_id = '999'))

[maltrecho]

Do you think it's going to be faster (or better) that way if for instance there are 100,000 rows in the table and it returns:

first_count = 13,000
second_count = 800

knowing that this query will be run very often?

[maltrecho]

Very easily, like so:

(SELECT COUNT(*) AS first_count
FROM this_table
WHERE (first_field_user_id = '999'))
UNION
(SELECT COUNT(*) AS second_count
FROM this_table
WHERE (second_field_user_id = '999'))

That is not working. The query returns:

---------------
| first_count |
---------------
| 18 |
| 21 |
---------------

While what I need is:

------------------------------
| first_count | second_count |
------------------------------
| 18 | 21 |
------------------------------

MySQL 4.1.10a

[vinyl-junkie]

This is just a guess, but from what you're telling me, the counts were actually returned in an array. Why not just reference them as first_count(0) and first_count(1)? If that works, you'll probably want to use a different naming convention for your count variable though. Anyway, let us know if that works for you.

[raf]

i don't think you can get the counts in one record efficiently with a single query.
Do you think it's going to be faster (or better) that way if for instance there are 100,000 rows in the table and it returns:

first_count = 13,000
second_count = 800

knowing that this query will be run very often?

Hmm. Why don't you time it? That's the only way to know which aproach wouls be faster.
If these two columns are numerical (yours is a textcolumn? judging by the quotes) and indexed, then a count will be returned very quickly since count() is an optimized function.
I would think that
SELECT COUNT(first_field_user_id = 999) FROM this_table
would be faster then
SELECT COUNT(*) AS first_count
FROM this_table
WHERE (first_field_user_id = '999')
on MyIsam tables, but from timing-experiments in the past, i know this isn't always the case.

[maltrecho]

It's actually working the way vinyl-junkie said:

( SELECT COUNT(*) AS total, 'first' AS count
FROM this_table
WHERE (first_field_user_id = '999') )
UNION
( SELECT COUNT(*) AS total, 'second' AS count
FROM this_table
WHERE (second_field_user_id = '999')
)

------------------
| total | count |
------------------
| 18 | first |
| 21 | second |
------------------

I'm testing it with just few rows, so I'll let you know as soon as I have better testings. I'll also try raf's suggestion (the real query has some more complex "where" clause though). Thank you both. :thumbsup: