Wyszukiwanie po kluczach

0

Witam, mam bazę w której jest jedna tabela ze średnio ponad 200 mln rekordów która zawiera przykładowe kolumny:
id_row int(11),
Date date,
NbrA char(24),
NbrB char(24),
System varchar(12);

oraz kluczami:
PRIMARY KEY id_row,
KEY StartDate(StartDate),
KEY NbrA(NbrA),
KEY NbrB(NbrB),
KEY NbrA_2(NbrA,StartDate),
KEY NbrB_2(NbrB,StartDate),

W kolumnie Date jest data wystąpienia pewnego zdarzenia, NbrA zawiera do 24 znaków identyfikatora pewnego nadajnika, NbrB identyfikator odbiornika i System to oczywiście identyfikator systemu zawierającego jego nazwę. Tabela zawiera jeszcze inne dane które nie są jednak ważne.

Mam problem taki że gdy w WHERE podaję do wyszukania parametr z podzapytania to baza nie wykorzystuje kluczy tylko przeszukuje zawsze całą tabelę np:

SELECT StartDate, NbrA, NbrB, System 
FROM tbl 
WHERE 
StartDate>=CONCAT('',(SELECT Date FROM SysIF WHERE id=1),'') AND 
(NbrA like CONCAT('',(SELECT NbrA FROM SysIF WHERE id=1),'') OR NbrB like CONCAT('',(SELECT NbrB FROM SysIF WHERE id=1),'')) 

Zamiast użyć kluczy NbrA_2 i NbrB_2 baza szuka po całej tabeli i trwa to bardzo długo. Gdy podam na sztywno dane do wyszukania bez podzapytań to klucze są używane i trwa to o wiele szybciej np:.

SELECT StartDate, NbrA, NbrB, System 
FROM tbl 
WHERE StartDate>='2020-01-01' AND (NbrA like '123qaz' OR NbrB like '456wsx' ); 

Prośba o podpowiedź co robię źle i jak to poprawić :-)

1

A jak zmienisz na joina?

 SELECT tbl.*
 FROM tbl 
Innej jon sysif s on s.id=1 tbl.stardate>= s.date and (tbl.nrba like a.nrba or tbl.nrbb like s.nrb)

0

Nie wiem jak dokładnie wygląda twój index ale concat może być powodem nie używania indeksu. Nie wiem co to za baza ale niektóre baza wykonują podzapytanie dla każdego rekordu.

0

najpierw wytłucza co stało za decyzją aby tutaj CONCAT('',(SELECT Date FROM SysIF WHERE id=1),'') dodać CONCAT i łączyć datę z pustym stringiem?

0
Tomek Pycia napisał(a):

Nie wiem jak dokładnie wygląda twój index ale concat może być powodem nie używania indeksu. Nie wiem co to za baza ale niektóre baza wykonują podzapytanie dla każdego rekordu.

Bez CONCAT również nie używa indeksów.

0
abrakadaber napisał(a):

najpierw wytłucza co stało za decyzją aby tutaj CONCAT('',(SELECT Date FROM SysIF WHERE id=1),'') dodać CONCAT i łączyć datę z pustym stringiem?

W DB poprzez formularz WWW są wyszukiwane rekordy które zawierają dane dla NbrA lub NbrB od zadanej StartDate. Dane z formularza do wyszukania są zapisywane do tabeli SysIF i stąd pobierane przy wyszukiwaniu w tabeli z danymi. Date, NbrA i NbrB nigdy nie są puste więc zawsze zwracany jest string. Doklejam '' do wyszukiwanych parametrów bo user ma prawo użyć maski % w NbrA i NbrB.

1

no to chyba się nie dziwisz, że jak szukasz po stringu a indeks masz na typie date to ten indeks nie jest brany pod uwagę. Dodatkowo LIKE tylko w niektórych bazach korzysta z indeksu i to tylko dla części przed pierwszym wystąpieniem wildchara.

0

A tak:

SELECT t.StartDate, t.NbrA, t.NbrB, t.System 
FROM tbl t JOIN  (SELECT Date, NbrA, NbrB FROM SysIF WHERE id=1) x
   ON t.StartDate>=x.Date AND (t.NbrA Like x.NbrA OR t.NbrB Like x.NbrB)

?

0
Marcin.Miga napisał(a):

A tak:

SELECT t.StartDate, t.NbrA, t.NbrB, t.System 
FROM tbl t JOIN  (SELECT Date, NbrA, NbrB FROM SysIF WHERE id=1) x
   ON t.StartDate>=x.Date AND (t.NbrA Like x.NbrA OR t.NbrB Like x.NbrB)

?

Zapytanie działa ale szuka tylko po indeksach StarDate

+------+-------------+------------------+-------+---------------+---------+---------+------+-----------+-------------------------------------------------+
| id   | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows      | Extra                                           |
+------+-------------+------------------+-------+---------------+---------+---------+------+-----------+-------------------------------------------------+
|    1 | PRIMARY     | <derived2>       | ALL   | NULL          | NULL    | NULL    | NULL |         2 |                                                 |
|    1 | PRIMARY     | t          		| ALL   | StartDate     | NULL    | NULL    | NULL | 148883212 | Range checked for each record (index map: 0x20) |
|    2 | DERIVED     | SysIF			| index | NULL          | created | 6       | NULL |        13 | Using where                                     |
+------+-------------+------------------+-------+---------------+---------+---------+------+-----------+-------------------------------------------------+
3 rows in set (0.000 sec)
0
abrakadaber napisał(a):

no to chyba się nie dziwisz, że jak szukasz po stringu a indeks masz na typie date to ten indeks nie jest brany pod uwagę. Dodatkowo LIKE tylko w niektórych bazach korzysta z indeksu i to tylko dla części przed pierwszym wystąpieniem wildchara.

Nie jestem fachowcem w DB więc pewne rzeczy mogą mnie dziwić lub o nich nie wiedzieć :-).
Baza szuka po indeksie złożonym ze NbrA i StartDate ale tylko gdy szukane dane są podane na sztywno:

EXPLAIN
SELECT StartDate, NbrA, NbrB, System
FROM t
WHERE StartDate>= '2020-03-18' AND 
(NbrA LIKE 'iFBkSzx6ju76' OR NbrB LIKE 'iFBkSzx6ju76');
+------+-------------+-------+-------------+-----------------------------------+---------------+---------+------+------+----------------------------------------------+
| id   | select_type | table | type        | possible_keys                     | key           | key_len | ref  | rows | Extra                                        |
+------+-------------+-------+-------------+-----------------------------------+---------------+---------+------+------+----------------------------------------------+
|    1 | SIMPLE      | t     | index_merge | StartDate,NbrA,NbrB,NbrA_2,NbrB_2 | NbrA_2,NbrB_2 | 75,75   | NULL |    7 | Using sort_union(NbrA_2,NbrB_2); Using where |
+------+-------------+-------+-------------+-----------------------------------+---------------+---------+------+------+----------------------------------------------+
1 row in set (0.001 sec)

Podanie danych z wyniku podzapytania lub wstawienie maski % powoduje już przeszukanie tylko indeksu StartDate:

EXPLAIN
SELECT StartDate, NbrA, NbrB, System
FROM t,(SELECT Date,Nbr FROM SysIF WHERE created_by=42 ORDER BY created DESC LIMIT 1) as x
WHERE StartDate>= CONCAT('',x.Date,'') AND (NbrA LIKE CONCAT('',x.Nbr,'') OR NbrB LIKE CONCAT('',x.Nbr,''));
+------+-------------+------------+-------+---------------+---------+---------+------+------+------------------------------------------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                                          |
+------+-------------+------------+-------+---------------+---------+---------+------+------+------------------------------------------------+
|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    2 |                                                |
|    1 | PRIMARY     | t          | ALL   | StartDate     | NULL    | NULL    | NULL |  500 | Range checked for each record (index map: 0x4) |
|    2 | DERIVED     | SysIF      | index | NULL          | created | 6       | NULL |   13 | Using where                                    |
+------+-------------+------------+-------+---------------+---------+---------+------+------+------------------------------------------------+
3 rows in set (0.000 sec)
0

Potrzebuję zmniejszyć czas przeszukania tabelki, obecnie wyszukanie rekordów trwa około 10 minut :-(

0

Jeżeli masz wildcard na początku lub gdzieś w środku to index nie pomoże. Faktyczne przyspieszenie uzyskasz zakładajac Full Text Index i używając MATCH() AGAINST().
Bo ciągle nie wiemy jakiej wartości szukasz, czyli co zwraca nbr z zapytania:

SELECT Date,Nbr FROM SysIF WHERE created_by=42 ORDER BY created DESC LIMIT 1

To da odpowiedź czy index w ogóle zostanie użyty.

1 użytkowników online, w tym zalogowanych: 0, gości: 1