MySQL — Медленная операция COUNT(*) / COUNT(id)

Жил я себе спокойно, разрабатывал приложения, углубился в базы данных, почитал про highload, и умные люди на конференции в прошлом году заявляли, что в PgSQL операция count(id) весьма медленная. Интересно, никогда об этом не думал, что там — посчитать по индексу… а, нет, не по индексу, полный скан, значит не так уж и быстро…

В большинстве случаев, не требуется знать точное значение count() для таблицы, когда нет условий на выборку оптимальнее использовать вместо классического

SELECT count(id) FROM <table>

запрос на статистику таблицы:

show table status like '<table name>';

Я тестировал на таблицы в ~20 тыс строк, скорость работы отличается значительно

  • 0,63 ms для обычного точного count, при этом реальное число 21902
  • 0,47 ms для статуса таблицы, а тут число 22008

То есть, чем таблица больше тем актуальнее использовать приближенный count вместо живого-настоящего.

 

ORDER BY RAND() in Doctrine without implement a new Doctrine function

Предположим, что нам нужно выбрать N строк из базы данных и отсортировать их случайным образом. При этом у нас нет возможности (или желания) реализовывать функцию rand() в Doctrine. 

В MySQL эта задача решается очень просто:

SELECT column FROM table
ORDER BY RAND()
LIMIT 10

Но если вы используете DQL (Doctrine Query Language) это будет не так просто. Следуя документации вы можете реализовать расширение для Doctrine и добавить инструкцию RAND в запросы, но мы не будем этого делать.

Для нашего решения мы будем использовать конструкцию where IN и будем искать что-нибудь (в данном случае ID или другое поле с автоинкриметом, если ваша таблица не содержит первичного ключа с автоинкриментом, вы должны реализовывать RAND расширение для Doctrine) в соответствии с случайными числами полученными при помощи простой функции на php.

В данном примере, таблица имеет поле с автоинкриментом, которое называется id (которая содержит в себе числовые значения), что бы получить случайные записи нам нужно для начала создать функцию, которая возвращает случайные числа в заданном диапазоне (начальное значение, максимальное значение и количество), например, такую как эта:

function UniqueRandomNumbersWithinRange($min, $max, $quantity) {
$numbers = range($min, $max);
shuffle($numbers);
return array_slice($numbers, 0, $quantity);
}

UniqueRandomNumbersWithinRange выдаст нам числа в заданном диапазоне, эти числа мы будем использовать что бы искать случайные строки в нашей таблице при помощи Doctrine, вот так:

$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('AppBundle:EntityName');
$quantity = 5;
$totalRowsTable = $repo->createQueryBuilder('a')->select('count(a.id)')->getQuery()->getSingleScalarResult();

$randomIds = UniqueRandomNumbersWithinRange(1,$totalRowsTable,$quantity);

$random_articles = $repo->createQueryBuilder('a')
->where('a.id IN (:ids)') // если у вас другое поле - поменяйте это
->setParameter('ids', $randomIds)
->setMaxResults(3)// Добавьте эту строку если вы хотите получить ограниченное количество записей (Если все IDs существуют вам будет нужно ограничение)
->getQuery()
->getResult();

Эта статья является вольным переводом этой

T-SQL: Таблицы Inserted/Deleted

Полезная такая шпаргалка:

Изменения во временных таблицах при срабатывании триггеров в T-SQL:

Operation Table Inserted Table Deleted
Min Rows Max Rows Min Rows Max Rows
Insert 1 0 0
Update 1 N 1 N
Delete 0 0 1 N

Где N — количество строк в таблице, к которой привязан триггер

Еще раз о RAND в SQL

В предыдущем посте я писал о том, что можно сделать функцию RAND, для генирации в диапазоне, однако при тестировании выяснилось, что она не работает должным образом — выкидываемое значение было больше чем max, в параметре функции.

Новая функция:

CREATE FUNCTION getRandom(@min int, @max int)
RETURNS INT
AS
BEGIN
Return (SELECT ROUND(((@max - @min -1) * V + @min), 0) FROM dbo.vRand)

END

SQL: Обертка на RAND()

В SQL есть функция RAND() которая может генерировать псевдослучайные числа.  Например такой запрос:

Select Rand()

Вернет:

rand

Дальше можно немного модифицировать выборку. Скажем, нам нужно, что бы выбиралось целое число от 0 до 100, тогда запрос будет таким:

SELECT FLOOR(100*RAND());

Ну и если от 10 до 100, то таким:

SELECT FLOOR(100*RAND()+10);

В языке SQL поддерживаются функции, так почему бы не написать обертку на Rand, которая будет генерировать числа от и до нужных нам границ?

Continue reading

SQL: Получить дату и время

Вот несколько способов получить текущую дату и время в Microsoft SQL Server. Можно использовать: CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP это не недетерминированная функция. Представления и выражения, которые ссылаются  на эту колонку не могут быть проиндексированны. CURRENT_TIMESTAMP может использоваться что бы вывести текущую дату и время  в отчетах.

Continue reading