Sql оптимизация на заявки

Опитвам се да оптимизирам SQL заявка. Проблемът е, че е много бавен! Работя с sqlite и със средно големи набори от данни (20000 реда). Моят sql синтаксис изглежда така:

SELECT DISTINCT date(A.last_update) as lastUpdate,
(SELECT COUNT(*) FROM bgp_update AS B WHERE date(B.last_update) = date(A.last_update) AND B.validity = 1) AS valid,
(SELECT COUNT(*) FROM bgp_update AS B WHERE date(B.last_update) = date(A.last_update) AND B.validity = 0) AS invalid,
(SELECT COUNT(*) FROM bgp_update AS B WHERE date(B.last_update) = date(A.last_update) AND B.validity = -1) AS notFound
FROM (SELECT DISTINCT last_update FROM bgp_update ORDER BY last_update) AS A WHERE last_update BETWEEN '%var' and '%var2';

По-долу е даден пример за това, което имам:

id   |     last_update   |  Validity

48   | 2009-1-6 18:34:38 |  notFound   
47   | 2009-1-6 18:34:38 |  valid   
46   | 2009-1-6 18:34:38 |  valid    
45   | 2009-1-3 18:34:38 |  invalid   
44   | 2009-1-3 18:34:38 |  invalid   
42   | 2009-1-4 18:34:38 |  notFound 
41   | 2009-1-4 18:34:38 |  notFound
48   | 2009-1-4 18:34:38 |  valid

И резултатът от заявката ще изглежда така:

    Date   |  valid   |  invalid   |    notFound

  2009-1-3 |   0      |     2      |       0
  2009-1-4 |   1      |     0      |       2
  2009-1-6 |   2      |     0      |       1

Имам нужда от това, за да генерирам линейна графика от него. Примерът би бил: линейна диаграма!

Но както споменах, заявката е много бавна! Как мога да го направя по-бързо?

:::: 2. РЕДАКТИРАНЕ :::::

Предишните описания опростих проблема. Изявлението ми за създаване на таблица изглежда така:

CREATE TABLE bgp_update
    (id INTEGER PRIMARY KEY,
    ip VARCHAR(64) NOT NULL,
    mask INTEGER NOT NULL,
    asn INTEGER NOT NULL,
    validity INTEGER NOT NULL,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

Както можете да видите, моята заявка използва само last_update и полетата за валидност.


sqlite query-optimization
person Viktor    schedule 27.01.2013    source източник
comment
Е, не уточнявате какъв е проблемът, какви са очакваните резултати, схема на таблица, индекси и т.н. Ние не сме четци на мисли.   -  person OldProgrammer    schedule 27.01.2013
comment
Съжалявам, ще редактирам въпроса.   -  person Viktor    schedule 27.01.2013
comment
Какво ви казва оптимизаторът на заявки?   -  person    schedule 27.01.2013
comment
Трябва да ни покажете дефинициите на таблицата и индекса. Диагностицирането на бавни заявки изисква пълни дефиниции на таблица и индекс, а не само описание или парафраза. Може би вашите таблици са дефинирани зле. Може би индексите не са създадени правилно. Може би нямате индекс в тази колона, която си мислехте, че имате. Без да виждаме дефинициите на таблицата и индекса, не можем да кажем. Ако знаете как да направите EXPLAIN, поставете резултатите и във въпроса.   -  person Andy Lester    schedule 27.01.2013
comment
date('2009-1-6 18:34:38') ще бъде NULL, защото месецът и денят не са подплатени с нула. Вашите примерни данни наистина ли са правилни?   -  person CL.    schedule 27.01.2013

Отговори (3)


arrow_upward
2
arrow_downward

Можете да използвате sum вместо case statements, напр.:

select last_update,
  sum(case when validity=1 then 1 else 0 end) valid,
  sum(case when validity=0 then 1 else 0 end) invalid,
  sum(case when validity=-1 then 1 else 0 end) notfound
from bgp_update
where last_update between '1/1/2013' and '1/2/2013'
group by last_update
order by last_update

SQL Fiddle

person gordy    schedule 27.01.2013
comment
Това ще има множество записи на ден, колоната validity не съдържа 0/1/-1 стойности и форматът на датата е грешен. - person CL.; 27.01.2013

arrow_upward
3
arrow_downward

Ако приемем, че стойностите на датата са в правилния формат, за да бъдат разбрани от функцията date, вие може просто да сумира условието(ята), което искате да проверите (тъй като булевите изрази просто връщат 0 или 1):

SELECT date(last_update) AS Date,
       sum(validity = 'valid'   ) AS valid,
       sum(validity = 'invalid' ) AS invalid,
       sum(validity = 'notFound') AS notFound
FROM bgp_update
WHERE last_update BETWEEN ? AND ?
GROUP BY date(last_update)
ORDER BY date(last_update)

Тази заявка ще бъде ефективна, ако имате индекс в колоната last_update, или дори малко по-ефективна, ако имате покриващ индекс на колоните last_update и validity.

person CL.    schedule 27.01.2013
comment
+1 за sum(validity=..) работи вместо sum(case when ... end) и изглежда много по-чисто - person gordy; 28.01.2013

arrow_upward
0
arrow_downward

Вложените подзаявки понякога правят заявката много неефективна. Може да обмислите пренаписване на заявката, така че да използва съединения.

Второ, можете да пренаредите обединените елементи, където таблицата с най-малко записи е най-близо до началото на заявката. Това ще намали броя на записите, които MySQL ще трябва да сортира, за да извлече редовете от други таблици.

Освен това може да обмислите използването на индекси на колони с идентификатори и колони на таблици, които имат конкретни стойности, които търсите.

Ако всичко друго се провали, може да откриете, че три отделни заявки може да са най-ефективни.

person osoclever    schedule 27.01.2013