26 февраля 2011
Очень удивился, когда не нашёл внятной схемы синтаксиса и работы JOIN в SQL. Нарисовал свою:
FULL JOIN. Эмулируется через UNION.CROSS JOIN эквивалентен INNER JOIN. Можно использовать как один, так и другой. В стандарте SQL это не так: INNER JOIN используется с ON, CROSS JOIN — без.RIGHT OUTER JOIN.PDF версии:
11 февраля 2011
Сегодня Qiang реализовал ещё одну интересную штуку для Yii: кеширование запросов к базе данных на всех уровнях от DAO до ActiveRecord.
$sql = 'SELECT * FROM tbl_post LIMIT 20'; // кешируем результат запроса 1000 секунд $rows = Yii::app()->db->cache(1000)->createCommand($sql)->queryAll();
Если данные в таблице меняются, можно попробовать инвалидировать кеш, используя менее затратный запрос:
$sql = 'SELECT * FROM tbl_post LIMIT 20'; // сбрасываем кеш, если результат изменился $dependency = new CDbCacheDependency('SELECT MAX(update_time) FROM tbl_post'); // кешируем на 1000 секунд $rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();
С AR это работает так:
$posts = Post::model()->cache(1000)->findAll(); $posts = Post::model()->cache(1000)->with('author')->findAll();
Опробовать можно, забрав код из SVN. Войдёт в релиз 1.1.7.
10 января 2011
Задача «выбрать 10 случайных постов» часто решается так:
SELECT * FROM post ORDER BY RAND() LIMIT 10;
Для совсем небольших проектов это будет работать, но когда проект начнёт расти начнутся проблемы. И начнутся они уже с 1500 записей. На моей рабочей машине этот запрос выполняется где-то 400мс.
EXPLAIN показывает нам Using temporary; Using filesort, что означает создание временной таблицы (а для большого количества записей она ещё и на диск пишется) и не использование индекса. Виновник, как вы уже догадались — ORDER BY RAND().
Чтобы использовать индекс можно выбрать не все данные, а только id:
SELECT id FROM post ORDER BY RAND() LIMIT 10
Теперь имея 10 id получим посты:
SELECT post.* FROM ( SELECT id FROM post ORDER BY RAND() LIMIT 10 ) AS ids JOIN post ON post.id = ids.id
Получаем результат примерно за 10мс, что несомненно лучше. Но и этого может не хватить.
В этом случае можно поступить следующим образом:
Далее делаем rand(1, 20) на сервере и выполняем очень простой и быстрый запрос:
SELECT * FROM random_post WHERE random_id = :random_id
Если нужно ещё быстрее — делаем аналог на Redis или memcached.
22 декабря 2010
Вышел релиз Doctrine 2 — передового PHP ORM и слоя абстракции данных.
3 октября 2010
Отличный бесплатный набор тестов по SQL от самых простых до довольно сложных. Существует уже довольно давно и хорошо себя зарекомендовал. Имеется рейтинг. После прохождения возможно получить сертификат «SQL Data Manipulation Language Specialist». Языки задач — русский и английский.
Рекомендуется к изучению всем, кто работает с реляционными базами данных.
10 марта 2010
Существует множество универсальных решений данной задачи, но в MySQL всё решается ещё проще:
ALTER IGNORE TABLE my_table ADD UNIQUE INDEX(a, b);
7 августа 2009
Довольно часто требуется получить ID только что вставленной в базу записи. В MySQL это чаще всего решается при помощи LAST_INSERT_ID.
В PostgreSQL можно сделать так:
INSERT INTO "Post"( title, text ) VALUES ( 'Я заголовок', 'Я текст' ) RETURNING id
11 марта 2009
Сразу предупреждаю, что эффективней использовать несколько «классических» запросов и, по возможности, транзакции (которые MyISAM не поддерживает). Так что… для общего развития:
UPDATE tbl_country SET price = CASE WHEN code = 1 THEN 123; WHEN code = 2 THEN 456; … END WHERE code IN (1,2,…)
INSERT INTO tbl_country (code, price) VALUES (1, 123), (2, 456), … ON DUPLICATE KEY UPDATE tbl_country.price = VALUES(price);
5 марта 2009
Задача: выбрать новости с id = [2, 10, 3, 88, 23] соблюдая порядок.
SELECT * FROM news WHERE id IN (2, 10, 3, 88, 23) ORDER BY FIELD (id, 2, 10, 3, 88, 23)
18 февраля 2009
Что только не придумают иногда программисты… Задача — изменить значение cron в таблице ml на противоположное. Код немного не SQL т.к., скорее всего, писался под Drupal:
UPDATE {ml} SET cron = (SELECT if(cron = 0,"1","0")) WHERE yid = %d
Хотя на самом деле можно проще, быстрее и понятней:
UPDATE {ml} SET cron = !cron WHERE yid = %d
