[mysql] (4.1 lub nowszy) pobieranie =?UTF-8?Q?rekord=C3=B3w?="minimalnych"
hubert depesz lubaczewski - 13-02-2007 00:04
[mysql] (4.1 lub nowszy) pobieranie =?UTF-8?Q?rekord=C3=B3w?="minimalnych"
hej
1. zajrzałem do faq - nie widzę tam tego
2. wiem jak zapisać w postgresie, wiem jak zapisać (nieoptymalnie) w
mysql'u.
szczegóły:
tabelka "t" o kolumnach: id, kod, kiedy
id jest kluczem podstawowym, dodatkowo mamy pewność, że kombinacja (kod,
kiedy) jest unikatowa.
dane są zasadniczo codziennie (kiedy ma granulację 1 dnia), ale nie
codziennie dla wszystkich kodów.
chciałbym dostać listę rekordów dla wszystkich możliwych kodów, z
pierwszym "kiedy" po określonej dacie.
czyli. dla tabelki:
id | kod | kiedy
----+-----+------------
1 | a | 2007-02-01
2 | b | 2007-02-02
3 | c | 2007-01-31
4 | b | 2007-02-17
i warunkiem od daty '2007-02-01' chcę dostać:
id | kod | kiedy
----+-----+------------
1 | a | 2007-02-01
2 | b | 2007-02-02
(rekord z id 3 nie, bo jest wcześniej niż 2006-02-01, a rekord z id = 4
nie, bo jest później, ale jest rekord 2 który jest wcześniej).
mam nadzieję, że to jasne.
jak to zrobić optymalnie w mysql'u? jeśli da się lepiej w wersji 5.0 czy
5.1 - fajnie, ale proszę o zaznaczenie tego od której wersji to
zadziała.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
Michal Jankowski - 13-02-2007 00:04
hubert depesz lubaczewski <depesz@depesz.com> writes:
> 2. wiem jak zapisać w postgresie, wiem jak zapisać (nieoptymalnie) w
> mysql'u.
A mógłbyś - dla potomności - obie wersje podać?
MJ
hubert depesz lubaczewski - 13-02-2007 00:04
On 2007-02-12, Michal Jankowski <michalj@fuw.edu.pl> wrote:
>> 2. wiem jak zapisać w postgresie, wiem jak zapisać (nieoptymalnie) w
>> mysql'u.
> A mógłbyś - dla potomności - obie wersje podać?
w postgresie:
select distinct on (kod) * from t where kiedy >= '2007-02-01' order by
kod, kiedy asc;
w mysql'u, ale raczej nieoptymalnie:
select t1.* from t t1 where t1.kiedy >= '2007-02-01' and not exists
(select * from t t2 where t2.kod = t1.kod and t2.kiedy < t1.kiedy and
t2.kiedy >= '2007-02-01');
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
Rafalsxat - 13-02-2007 00:04
=?UTF-8?Q?Re:_=5Bmysql=5D_=284.1_lub_nowszy=29_pob?= =?UTF-8?Q?ieranie_rekord=C3=B3w__=22minimalnych=22?=
> czyli. dla tabelki:
> id | kod | kiedy
> ----+-----+------------
> 1 | a | 2007-02-01
> 2 | b | 2007-02-02
> 3 | c | 2007-01-31
> 4 | b | 2007-02-17
>
> i warunkiem od daty '2007-02-01' chcę dostać:
> id | kod | kiedy
> ----+-----+------------
> 1 | a | 2007-02-01
> 2 | b | 2007-02-02
>
> (rekord z id 3 nie, bo jest wcześniej niż 2006-02-01, a rekord z id = 4
> nie, bo jest później, ale jest rekord 2 który jest wcześniej).
czysta mysl, moze dziala:
select *
from (select *
from t
where kiedy >= '2007-02-01'
order by kiedy asc)
group by kod
pozdr
Raf
--
Archiwum grupy: http://niusy.onet.pl/pl.comp.bazy-danych
hubert depesz lubaczewski - 13-02-2007 00:04
On 2007-02-12, Rafalsxat <gonzak@op.pl> wrote:
> select *
> from (select *
> from t
> where kiedy >= '2007-02-01'
> order by kiedy asc)
> group by kod
hmm .. działa.
wygląda na to, że group by w tym przypadku działa tak jak distinct on.
czy to jest jakoś gdzieś udokumentowane?
i dodatkowo - czy taki "group by" jest robiony przed czy po sortowaniu?
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
Michal Jankowski - 13-02-2007 00:04
hubert depesz lubaczewski <depesz@depesz.com> writes:
> hmm .. działa.
> wygląda na to, że group by w tym przypadku działa tak jak distinct on.
To jest znany ficzer mysql, że
select c1, c2 from t group by c1;
działa na kształt
select c1, first(c2) from t group by c1;
albo inaczej
select distinct on (c1) c1, c2 from t;
> czy to jest jakoś gdzieś udokumentowane?
A czort go wie 8-).
MJ
Maciek Dobrzanski - 13-02-2007 00:04
"hubert depesz lubaczewski" <depesz@depesz.com> wrote in message
news:hbd6a4-p5p.ln1@xxx.home.depesz.com...
>> select *
>> from (select *
>> from t
>> where kiedy >= '2007-02-01'
>> order by kiedy asc)
>> group by kod
>
> hmm .. działa.
> wygląda na to, że group by w tym przypadku działa tak jak distinct on.
>
> czy to jest jakoś gdzieś udokumentowane?
A mnie swoją drogą przyszedł do głowy inny, chytry plan wynikający ze
sposobu w jaki MySQL korzysta z danych i z indeksów. Na starcie mamy tabelę
w najprostszej postaci, wypełnioną kilkudziesięcioma tysiącami wierszy
(dokładnie 27748).
CREATE TABLE `t12` (
-> `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> `kod` char(1) NOT NULL,
-> `kiedy` date NOT NULL);
Dla testów i lepszego obrazu wyników usunąłem z niej wszystkie te, które
spełniają:
kod = 'c' AND kiedy >= '2007-02-01'
kod = 'b' AND kiedy BETWEEN '2007-02-01' AND '2007-02-03'
Oczywiście w najprostszej formie zapytanie nie zwróci oczekiwanych wyników,
tj.
SELECT * FROM t12 WHERE kiedy >= '2007-02-01' GROUP BY kod;
+----+-----+------------+
| id | kod | kiedy |
+----+-----+------------+
| 8 | a | 2007-02-04 |
| 3 | b | 2007-03-12 |
| 15 | d | 2007-02-01 |
+----+-----+------------+
Dane w `id` czy `kiedy` są "przypadkowe". Można więc spróbować się
posiłkować indeksem, który jako taki jest posortowany:
ALTER TABLE t12 ADD INDEX k_kiedy_kod (kiedy, kod);
Jednak nadal nie ma rezutlatów:
SELECT * FROM t12 WHERE kiedy >= '2007-02-01' GROUP BY kod;
+----+-----+------------+
| id | kod | kiedy |
+----+-----+------------+
| 8 | a | 2007-02-04 |
| 3 | b | 2007-03-12 |
| 15 | d | 2007-02-01 |
+----+-----+------------+
Dlaczego?
EXPLAIN SELECT * FROM t12 WHERE kiedy >= '2007-02-01' GROUP BY kod\G
id: 1
select_type: SIMPLE
table: t12
type: ALL
possible_keys: k_kiedy_kod
key: NULL
key_len: NULL
ref: NULL
rows: 28050
Extra: Using where; Using temporary; Using filesort
Poprostu indeks nie jest wykorzystany. Pomagam więc optymalizatorowi.
EXPLAIN SELECT * FROM t12 FORCE INDEX (k_kiedy_kod) WHERE kiedy >=
'2007-02-01' GROUP BY kod\G
id: 1
select_type: SIMPLE
table: t12
type: range
possible_keys: k_kiedy_kod
key: k_kiedy_kod
key_len: 3
ref: NULL
rows: 18469
Extra: Using where; Using temporary; Using filesort
Dobrze. A jak prezentuje się wynik tego zapytania?
SELECT * FROM t12 FORCE INDEX (k_kiedy_kod) WHERE kiedy >= '2007-02-01'
GROUP BY kod;
+-----+-----+------------+
| id | kod | kiedy |
+-----+-----+------------+
| 162 | a | 2007-02-01 |
| 26 | b | 2007-02-03 |
| 15 | d | 2007-02-01 |
+-----+-----+------------+
Czyli dokładnie to, co jest oczekiwane. Pierwszy w kolejności po dacie wpis
dla każdego kodu, jeśli data jest późniejsza niż '2007-02-01'. MySQL
"przejechał się" po posortowanym wg. naszego życzenia indeksie i w tej
dokładnie kolejności wyciągnął dane.
Różnica w wydajności obu wersji?
Czas wykonywania:
Moja
0.14s
Rafalsxat
0.29s
Operacje:
Moja
| Handler_read_key | 1 |
| Handler_read_next | 17993 |
| Handler_read_rnd | 3 |
| Handler_read_rnd_next | 5 |
| Handler_write | 17993 |
Rafalsxat
| Handler_read_rnd | 3 |
| Handler_read_rnd_next | 46050 |
| Handler_write | 35986 |
Oczywiście moja wersja nie jest tak uniwersalna, bo np. nie da się wyciągnąć
najstarszych wpisów. No i nie ma gwarancji, że wraz z wersją bazy coś się
nie zmieni.
Pozdrawiam,
Maciek
Maciek Dobrzanski - 13-02-2007 00:04
"Maciek Dobrzanski" <svc.usenet@posterus.com> wrote in message
news:eqqmld$o4t$1@garfield.posterus.com...
> SELECT * FROM t12 FORCE INDEX (k_kiedy_kod) WHERE kiedy >= '2007-02-01'
> GROUP BY kod;
Co ciekawe przy silniku InnoDB optymalizator sam wybiera wykorzystanie tego
indeksu bez dodatkowego przymuszania. Ma to zapewne związek z tym, że koszt
dostępu do danych kolumny `id` poprzez indeks `k_kiedy_kod` jest w zasadzie
zerowy.
Maciek
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[mysql] =?ISO-8859-2?Q?Za=E6mienie=2E=2E=2E_jak_wy=B6wietli=E6?==?ISO-8859-2?Q?=2E=2E=2E?=
[mysql] =?ISO-8859-2?Q?wielko=B6=E6_bazy_a_stabilno=B6=E6=2C?==?ISO-8859-2?Q?_podzia=B3_du=BFej_bazy_a_powi=B1zania_tabel?=
[mysql 4.0.x] przenoszenie kolum =?ISO-8859-2?Q?mi=EAdzy_bazam?==?ISO-8859-2?Q?i_cd_=2E=2E=2E_?=
[MySQL] =?ISO-8859-2?Q?z=B3=B1czenie_tabeli_u=BFytkownik_i?==?ISO-8859-2?Q?_zdj=EAcia_z_wyborem_zdj=EAcia_domy=B6lnego?=
[MySQL] Jak =?ISO-8859-2?Q?wpisa=E6_do_tabeli_pozycje_dl?==?ISO-8859-2?Q?a_wierszy_gdybym_te_wiersze_wybiera=B3_w_ok?== ?ISO-8859-2?Q?re=B6lonej_kolejno=B6ci_=3F?=
Gdzie MySQL 4.1, a gdzie 5.0?
[MySQL 4.0...4.1] zabezpieczenie przed =?ISO-8859-2?Q?jednoczesn?==?ISO-8859-2?Q?=B1_edycj=B1?=
[MS SQL] "set names" (mySQL) w MS SQL
[mysql 5.x] jak =?ISO-8859-2?Q?zrealizowa=E6_zapytanie=3F_cz?==?ISO-8859-2?Q?yli_podzapytanie_i_wi=EAcej_ni=BF_jeden_rz=B1? ==?ISO-8859-2?Q?d_wynik=F3w?=
MySQL - jak =?ISO-8859-2?Q?wyeksportowa=E6_zawarto=B6=E6_wie?==?ISO-8859-2?Q?lkiej_tabeli?=
zanotowane.pldoc.pisz.plpdf.pisz.pllisinski.htw.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
