Эффективен ли этот SQLite Query?
я хорошо провожу время, немного программируя счетчик посетителей. это PHP5 / SQLite3 перемешать.
сделал две таблицы базы данных, один для посетители, и один для хиты. структура и пример данных:
CREATE TABLE 'visitors' (
'id' INTEGER DEFAULT NULL PRIMARY KEY AUTOINCREMENT,
'ip' TEXT DEFAULT NULL,
'hash' TEXT DEFAULT NULL,
UNIQUE(ip)
);
INSERT INTO "visitors" ("id","ip","hash") VALUES ('1','1.2.3.4','f9702c362aa9f1b05002804e3a65280b');
INSERT INTO "visitors" ("id","ip","hash") VALUES ('2','1.2.3.5','43dc8b0a4773e45deab131957684867b');
INSERT INTO "visitors" ("id","ip","hash") VALUES ('3','1.2.3.6','9ae1c21fc74b2a3c1007edf679c3f144');
CREATE TABLE 'hits' (
'id' INTEGER DEFAULT NULL PRIMARY KEY AUTOINCREMENT,
'time' INTEGER DEFAULT NULL,
'visitor_id' INTEGER DEFAULT NULL,
'host' TEXT DEFAULT NULL,
'location' TEXT DEFAULT NULL
);
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('1','1418219548','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('2','1418219550','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('3','1418219553','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('4','1418219555','2','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('5','1418219557','1','localhost','/some/path/example.php');
INSERT INTO "hits" ("id","time","visitor_id","host","location") VALUES ('6','1418219558','3','localhost','/some/path/example.php');
Теперь я хочу получить данные о посетителях, но только от тех, кто был активен в последние 30 секунд, например. мне нужны следующие данные в качестве вывода, здесь с идентификатором пользователя 1 в качестве примера:
$visitor = Array(
[id] => 1
[ip] => 1.2.3.4
[hash] => f9702c362aa9f1b05002804e3a65280b
[first_hit] => 1418219548
[last_hit] => 1418219557
[last_host] => localhost
[last_location] => /some/path/example.php
[total_hits] => 4
[idle_since] => 11
)
я получу это с моим текущим запросом, все хорошо, но, как вы можете видеть, мне нужно много подвыборов для этого:
SELECT
visitors.id,
visitors.ip,
visitors.hash,
(SELECT hits.time FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id ASC LIMIT 1) AS first_hit,
(SELECT hits.time FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS last_hit,
(SELECT hits.host FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS last_host,
(SELECT hits.location FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS last_location,
(SELECT COUNT(hits.id) FROM hits WHERE hits.visitor_id = visitors.id) AS total_hits,
(SELECT strftime('%s','now') - hits.time FROM hits WHERE hits.visitor_id = visitors.id ORDER BY hits.id DESC LIMIT 1) AS idle_since
FROM visitors
WHERE idle_since < 30
ORDER BY last_hit DESC
так, это нормально для моего варианта использования или вы знаете лучший подход для получения этих данных из этих двух таблиц? я уже играл с JOINS, но независимо от того, как я его подправил, COUNT () дал мне неправильные результаты, как например, у идентификатора пользователя 1 только один общий удар.
Я, вероятно, должен изменить модель базы данных, если я хочу использовать JOINS должным образом, я думаю.
Обновить: на основе AeroX ‘Ответ Я построил новый запрос. в основном это была только одна маленькая ошибка. Вы не можете иметь MAX () в предложении WHERE. используя HAVING сейчас после группировки.
Я также проверил как старый, так и новый с EXPLAIN и EXPLAIN QUERY PLAN. выглядит намного лучше. Спасибо вам, ребята!
SELECT
V.id,
V.ip,
V.hash,
MIN(H.time) AS first_hit,
MAX(H.time) AS last_hit,
strftime('%s','now') - MAX(H.time) AS idle_since,
COUNT(H.id) AS total_hits,
LH.host AS last_host,
LH.location AS last_location
FROM visitors AS V
INNER JOIN hits AS H ON (V.id = H.visitor_id)
INNER JOIN (
SELECT visitor_id, MAX(id) AS id
FROM hits
GROUP BY visitor_id
) AS L ON (V.id = L.visitor_id)
INNER JOIN hits AS LH ON (L.id = LH.id)
GROUP BY V.id, V.ip, V.hash, LH.host, LH.location
HAVING idle_since < 30
ORDER BY last_hit DESC
Решение
Вы, вероятно, хотите очистить это, но это должно дать вам представление о том, как сделать объединения и как использовать GROUP BY заявление, чтобы агрегировать ваши хиты таблицы для каждого посетителя. Это должно быть более эффективным, чем использование большого количества подзапросов.
Я включил комментарии к объединениям, чтобы вы могли понять, почему я делаю их.
SELECT
V.id,
V.ip,
V.hash,
MIN(H.time) AS first_hit,
MAX(H.time) AS last_hit,
COUNT(H.id) AS total_hits,
strftime('%s','now') - MAX(H.time) AS idle_since,
LH.host AS last_host,
LH.location AS last_location
FROM visitors AS V
-- Join hits table so we can calculate aggregates (MIN/MAX/COUNT)
INNER JOIN hits AS H ON (V.id = H.visitor_id)
-- Join a sub-query as a table which contains the most recent hit.id for each visitor.id
INNER JOIN (
SELECT visitor_id, MAX(id) AS id
FROM hits
GROUP BY visitor_id
) AS L ON (V.id = L.visitor_id)
-- Use the most recent hit.id for each visitor.id to fetch that most recent row (for last_host/last_location)
INNER JOIN hits AS LH ON (L.id = LH.id)
GROUP BY V.id, V.ip, V.hash, LH.host, LH.location
HAVING idle_since < 30
ORDER BY last_hit DESC
Другие решения
Одним из лучших способов измерения производительности запросов является использование explain,
От SQLite
Команда EXPLAIN QUERY PLAN SQL используется для получения высокого уровня
описание стратегии или плана, который SQLite использует для реализации
конкретный запрос SQL. Что наиболее важно, EXPLAIN QUERY PLAN сообщает о
способ, которым запрос использует индексы базы данных. Этот документ является
руководство по пониманию и интерпретации выходных данных EXPLAIN QUERY PLAN.
Справочная информация доступна отдельно:Notes on the query optimizer. How indexing works. The next generation query planner.Команда EXPLAIN QUERY PLAN возвращает ноль или более строк по четыре
столбцы каждый. Имена столбцов: «selectid», «order», «from»,
«Деталь». Первые три столбца содержат целочисленное значение. Финал
столбец «деталь», содержит текстовое значение, которое несет большую часть
Полезная информация.EXPLAIN QUERY PLAN наиболее полезен в операторе SELECT, но может также
появляться с другими операторами, которые читают данные из таблиц базы данных
(например, ОБНОВЛЕНИЕ, УДАЛЕНИЕ, ВСТАВИТЬ В … ВЫБРАТЬ).
Пример explain запрос:
EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;
http://www.tutorialspoint.com/sqlite/sqlite_explain.htm
Ниже приведены более сложные примеры использования.