Counting rows in multiple tables cause large delay

Question

I have 3 tables with mainly string data and unique id column:

categories ~45 rows
clientfuncs ~800 rows
serverfuncs ~600 rows

All tables have unique primary AI column 'id'. I try to count rows in one query:

SELECT COUNT(categories.id), COUNT(serverfuncs.id), COUNT(clientfuncs.id) FROM categories, serverfuncs, clientfuncs

It takes 1.5 - 1.7 s.

And when I try

SELECT COUNT(categories.id), COUNT(serverfuncs.id) FROM categories, serverfuncs

or

SELECT COUNT(categories.id), COUNT(clientfuncs.id) FROM categories, clientfuncs

or

SELECT COUNT(clientfuncs.id), COUNT(serverfuncs.id) FROM clientfuncs, serverfuncs

, it takes 0.005 - 0.01 s. (as it should be)

Can someone explain, what is the reason for this?

Answer 1

You're doing a cross join of 45*800*600 rows, you'll notice that when you check the result of the counts :-)

Try this instead:

SELECT 
  (SELECT COUNT(*) FROM categories), 
  (SELECT COUNT(*) FROM serverfuncs), 
  (SELECT COUNT(*) FROM clientfuncs);