Синтаксис нескольких COUNT(...) с разными условиями.

[general]

Синтаксис нескольких COUNT(...) с разными условиями.

Весь вечер провозился, никак не получается. Помогите.

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

$qstring = "SELECT count(id) as good, count (id = 4) as bad, count (id=5) as well  FROM stat"

Знаю, что синтаксис здесь неправильный. Каков должен быть синтаксис в этом случае? Можно ли объединить все это в один запрос?

 

[alpine]

самое простое в два запроса

1) SELECT id, count(id) as cnt WHERE id IN(4,5) GROUP BY id
2) SELECT count(*) as cnt WHERE id NOT IN(4,5)

-~{}~ 27.11.05 00:44:

Одним запросом не вижу смысла это делать. IMHO

 

[ForJest]

можно всё подсчитать одним запросом, но это будет гораздо тормознее, чем считать это же тремя запросами.

SELECT 
    COUNT(*) AS good, 
    SUM(id=4) AS bad,  
    SUM(id=5) AS well
FROM stat;

Но лучше сделать три запроса

 

[Falc]

ForJest
>>Но лучше сделать три запроса

Это почему?

1 запрос - 1 фулскан
3 запроса - 1 фулскан и 2 индекс скана (если индекс есть по id)

P.S. если первый запрос действительно можно заменить на SELECT count(*) FROM stat, то никакого фулскана там не будет.

 

[eddie]

SELECT count(*) as good, sum(if(id = 4,1,0)) as bad, sum(if(id=5,1,0)) as well FROM stat


good=good - bad - well

 

[alpine]

{ ... }

 

[chira]

SELECT
COUNT(*) AS good,
SUM(id=4) AS bad,
SUM(id=5) AS well
FROM stat;

если есть индекс по полю id, будет использоваться он, а не FULL table scan

 

[Falc]

chira
>>если есть индекс по полю id, будет использоваться он, а не FULL table scan

full index scan не сильно отличается от full table скана если размер строки индекса примерно равен строке таблицы.

 

[chira]

Falc

full index scan не сильно отличается от full table скана если размер строки индекса примерно равен строке таблицы.

это ты серьёзно?
работа с индексом == FULL table sacn?

 

[Steamroller]

работа с индексом == FULL table sacn?

Ну не == конечно, но все равно хреново очень.
select count(*) - мгновенный ответ дает, вообще не заходя в таблицу
select count(*) where id=4 - сканит ровно ту часть индекса, которая нужна.
То есть по-любому в разы быстрее будет, чем один запрос.

 

[chira]

Steamroller

согласен

 

[Falc]

chira
>>работа с индексом == FULL table sacn?

Не работа с индексом а полное сканирование индекса, у MySQL'я правда есть небольшое тут премущество т.к. индексы кешируются, с другой стороны full index scan может загнать в индекс совсем не нужные данные.

 

[ForJest]

Falc

full index scan не сильно отличается от full table скана если размер строки индекса примерно равен строке таблицы.

Меня очень заинтересовало твоё мнение по этому вопросу. Если это не "впечатление которое осталось", то
то, пожалуйста приведи материалы или ссылки на них подверждающие процитирвованное высказывание.
----------------------
Пока что я не понимаю как можно приравнять "скан индекса" к скану таблицы. Если индекс являет собою древовидную структуру и имеет кэш, в то время как таблица имеет последовательную структуру и не имеет кэша?
Опять же размер поля id - 2-3-4 байта никак не может быть сравним с размером записи.

 

[Falc]

ForJest
Еще раз говорю что это для высказывание для полного сканирования индекса (то что в оракле называется INDEX FAST FULL SCAN) при размере строк индекса примерно равном размеру строк таблицы, что при таблицах содержащих небольшое количество числовых полей весьма частая ситуачия (не забывайте что в индексе еще хранится указатель на саму строку, который обычно занимает 4 байта).

Итак:

Table full scan - это полный перебор всех записей с таблице, если считать что у нас 0% кеш попаданий то скорость упирается в дисковую подсистему и следовательно прямопропорцианальна размеру таблицы.

Index full scan - это полный перебор всех записей в индексе, опять же если считать что у нас 0% кеш попаданий то скорость упирается в дисковую подсистему и также прямопропорцианальна размеру индекса.

Теперь что касается MySQL - индексы кешируются сервером БД, а данные операционной системы, при грамотной настройке сервера базы обращение к индексам должно имень лучший % кеш попаданий, но если Index full scan выполняется для достаточно большого по размеру ( в мегабайтах ) индекса то это может негативно отразится на кеше индекса ( хотя я не знаю как устроен алгоритм кеширования индексов в MySQL и это только мое предположение). Но влюбом случае на большой таблице скорее всего все упрется в дисковую подсистему.

 

[ForJest]

Falc
Размышления по этому поводу безусловно интересны. Но, пожалуйста приведи хотя бы одну ссылку на документ или исследование, которые подтверждают твои размышления.
Про full index scan и его негативное влияние на производительность я слышу впервые касательно MySQL
--------------
Для справки - MySQL не использует индекс для записей, число которых превышает примерно треть от всего количества записей.
Поэтому да, в три запроса получится быстрее не всегда, а лишь если записей с id=4 и id=5 небольшое количество. В остальных случаях это будет full table scan.
--------------

 

[Falc]

ForJest
>>Для справки - MySQL не использует индекс для записей, число которых превышает примерно треть от всего количества записей.

Это невсегда верно. Это выплняется только для условия когда происходит после индексного доступа доступ к таблице, если доступа к таблице не происходит MySQL может перебрать хоть все строки индекса, что и называется полным сканированием индекса.

Доки для MySQL по этому вопросу я не видел. Сейчас у меня нету под рукой MySQL, но если хочешь, когда приду домой могу нарисовать пару запросов, которые должны подтвердить мои размышления.

По поводу конкретного примера (заданного вопроса автором топика) ничего говорить не буду так как там первый запрос может вообще не делать обращения к данным а сразу вытаскивать count и з метаданных.

-~{}~ 29.11.05 21:40:

Вот простенький пример в в котором index full scan работает значительно медленее table full scan'а:

drop table if exists t;

-- Создаем тестовую таблицу на оснавании любой из существующих таблиц с 1000 записей или больше
create table t ( f1 int NULL ) as
select 1 AS f1 from tb t1, tb t2 LIMIT 1000000;

-- Считаем COUNT без индекса
select count(f1) from t;


-- Создаем индекс
ALTER TABLE t ADD INDEX (f1);

-- Считаем COUNT с индексом
select count(f1) from t;

На моем компьютере первый "select count(f1) from t" в пять раз медленее

 

[Steamroller]

Автор оригинала: ForJest

Про full index scan и его негативное влияние на производительность я слышу впервые касательно MySQL

Вот тут например как раз эта проблема рассматривается:
http://dev.mysql.com/doc/refman/5.0/en/midpoint-insertion.html
То есть существует две настройки - key cache division limit и key cache age threshold, как раз чтобы один скан не выкидывал из буфера все нужное, но на практике даже это не помогает - если например на один mysqld с сайтами поселить базу mnogosearch'а, то хана, сайты тормозить начинают (там правда не только key buffer загаживается, еще и query cache).

 

[chira]

Falc

твой простенький пример не имеет никакого практического применения, поэтому, не может являтся доказательством применения твоего высказывания для реальных данных

index full scan работает значительно медленее table full scan'а

давай другой пример

 

[Falc]

chira
Мое высказывание:
>>index full scan работает значительно медленее table full scan'а

Касалось как раз только этого примера.


А основное мое высказывание немного другое и как раз касается более общего случая:
>>full index scan не сильно отличается от full table скана если размер строки индекса примерно равен строке таблицы.