Хочу сделать вот так:

SELECT
   firms.name_ru AS name_ru,
   firms.name_en AS name_en,
   firms.name_de AS name_de,
   firms.name_fr AS name_fr,
   firms.mnemo AS mnemo,
   (SELECT COUNT(*) FROM products WHERE ((products.hide = 'show') AND (products.id_firms = firms.id))) AS count_films
FROM firms, products
WHERE ((firms.hide='show') AND (count_films > 0)) ORDER BY firms.id LIMIT 0, 2332 

Выдаёт: #1054 - Unknown column 'count_films' in 'where clause'

Как использовать столбец count_films в условии? Спасибо.

давайте сначала засекем время.

$s = microtime(true);
..query..
$f = microtime(true);
$d = $f - $s;
echo $d;

Если все же минута, то, во-первых. Нужно сделать explain запроса (explain query). Избавиться от filesort, temporary files. Проследить чтобы было затронуто как можно меньше строк и проставить индексы

Во-вторых иногда горзада лучше сделать 2 запроса вместо одного.

$count_query = "SELECT COUNT(*) FROM products WHERE ((products.hide = 'show') AND (products.id_firms = firms.id))";

$second_query = " ....  WHERE count_query_result > 0"

Минута, даже намного больше минуты. explain запроса сделал, но что дальше делать пока не ясно. Вот explain запроса:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	firms 	ALL 	NULL 	NULL 	NULL 	NULL 	18650 	Using where; Using temporary; Using filesort
1 	PRIMARY 	products 	index 	NULL 	PRIMARY 	4 	NULL 	50974 	Using index; Using join buffer
2 	DEPENDENT SUBQUERY 	products 	ALL 	NULL 	NULL 	NULL 	NULL 	50974 	Using where

Убараю

ORDER BY firms.id

запрос выполняется в разы быстрее, но всё равно медленно.

SELECT firms.name_ru AS name_ru, firms.name_en AS name_en, firms.name_de AS name_de, firms.name_fr AS name_fr, firms.mnemo AS mnemo, (

SELECT COUNT( * )
FROM products
WHERE (
(
products.hide = 'show'
)
AND (
products.id_firms = firms.id
)
)
) AS count_films
FROM firms, products
WHERE firms.hide = 'show'
LIMIT 0 , 10

Выполнился за 8 секунд

Время показывает phpMyAdmin. Там запросы выполняю.

1 	PRIMARY 	firms 	ALL 	NULL 	NULL 	NULL 	NULL 	18650 	Using where; Using temporary; Using filesort

Вот эта самая страшная строка -)
Посмотрите, на столбец extra! Там присутствует filesort, using temporary.
Это смерть для Mysql. Потому что она(база данных) создаст файл с 19к записями ( в вашем случае ) и начнет их в файле сортировать. А если там будет мильэён )? Сортировка должна быть по индексу.

Если не знаем что это такое - читаем статью

P.S. И еще раз повторюсь. Не злоупотребляйте вложенными запросами. Иногда лучше сделать 2 отдельных!

Втыкнул индексы во все поля всех таблиц, которые связаны и айдишниками. Запрос без

ORDER BY firms.id

выполнился за пол секунды:

SELECT
   firms.name_ru AS name_ru,
   firms.name_en AS name_en,
   firms.name_de AS name_de,
   firms.name_fr AS name_fr,
   firms.mnemo AS mnemo,
   (SELECT COUNT(*) FROM products WHERE ((products.hide = 'show') AND (products.id_firms = firms.id))) AS count_films
FROM firms, products
WHERE ((firms.hide='show') AND (count_films > 0)) LIMIT 0, 2332

Ура!

Но такой запрос по прежнему выполняется очень и очень медленно:

SELECT
   firms.name_ru AS name_ru,
   firms.name_en AS name_en,
   firms.name_de AS name_de,
   firms.name_fr AS name_fr,
   firms.mnemo AS mnemo,
   (SELECT COUNT(*) FROM products WHERE ((products.hide = 'show') AND (products.id_firms = firms.id))) AS count_films
FROM firms, products
WHERE ((firms.hide='show') AND (count_films > 0)) ORDER BY firms.id LIMIT 0, 2332

Все индексы таблиц проставлены, запрос по прежнему медленный. Что делать? Спасибо.

Вот такой запрос с

ORDER BY firms.id

, работает за 0.3152 сек. Убрал 'products' во втором 'FROM':

SELECT
   firms.name_ru AS name_ru,
   firms.name_en AS name_en,
   firms.name_de AS name_de,
   firms.name_fr AS name_fr,
   firms.mnemo AS mnemo,
   (SELECT COUNT(*) FROM products WHERE ((products.hide = 'show') AND (products.id_firms = firms.id))) AS count_films
FROM firms
WHERE ((firms.hide='show') AND (count_films > 0)) ORDER BY firms.id LIMIT 0, 2332

все равно очень долго для такого кол-ва данных. У меня в базе 19кк идет выборка меньше чем за 0.02

Смотрите explain. Везде должны стоять индексы и кол-во затронутых строк (rows) не должно быть большим! По крайней мерие не больше чем у вас задан лимит, аля 2к. И еще. Индекс можно ставить не только на 1 поле. Можно и на 2 и больше. Пример: нужно выбрать какие-то айдишники и отсортировать их по дате.

SELECT * FROM Items WHERE ItemID IN(1, 3, 4, 5) ORDER BY `date` DESC

Тогда индекс должен быть такой: index_name (`ItemID`, `date`)