ostatni rekord z tablicy - problem z optymalizacją Opcje

[ChrisB]

witam jak w temacie potrzebuje pobrać ostatni rekord z tablicy

tablica to innodb z około 1k rekordów (w tym przypadku mała ale i prosta - mam też parę innych tabel gdzie jest więcej rekordów i ten sam problem)

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `statydzienne` (
  `ids` int(6) NOT NULL AUTO_INCREMENT,
  `data` date NOT NULL DEFAULT '0000-00-00',
i tam pare innych pól które nie maja aktualnie znaczenia
  PRIMARY KEY  (`ids`),
  KEY `data` (`data`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1037 ;
[SQL] pobierz, plaintext 

problem:

polecany wszędzie sposób pobierania ostatniego rekordu:

[SQL] pobierz, plaintext 
mysql> EXPLAIN SELECT * FROM statydzienne ORDER BY ids ASC LIMIT 1;
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | TABLE        | type  | possible_keys | KEY     | key_len | ref  | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | statydzienne | INDEX | NULL          | PRIMARY | 4       | NULL |  882 |       |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------+
1 row IN SET (0.00 sec)
 
[SQL] pobierz, plaintext 

czyli żebym otrzymał wynik - musi przeskanować cała tablice...

inny sposób:

[SQL] pobierz, plaintext 
mysql> EXPLAIN SELECT * FROM statydzienne WHERE ids=(SELECT max(ids) FROM statydzienne);
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | TABLE        | type  | possible_keys | KEY     | key_len | ref   | rows | Extra                        |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | statydzienne | const | PRIMARY       | PRIMARY | 4       | const |    1 |                              |
|  2 | SUBQUERY    | NULL         | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | SELECT TABLES optimized away |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+------------------------------+
2 rows IN SET (0.00 sec)
[SQL] pobierz, plaintext 

nie wiem czy lepiej bo nie do końca rozumiem tą drugą linijke:) ale przynajmniej z pierwszej wynika że tylko 1 wiersz skanuje - ten poprawny

opcja 3 to przechowywanie w tempie id ostatniego wiersza (jest on dodawany raz dziennie wiec w tym przypadku to nie jest problem) :

[SQL] pobierz, plaintext 
mysql> EXPLAIN SELECT * FROM statydzienne WHERE ids=777;
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | TABLE        | type  | possible_keys | KEY     | key_len | ref   | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | statydzienne | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

którą drogą powinienem iść? (zakładam że 1 mogę pominąć, więc jest 2 którą pod względem optymalności nie do końca rozumiem, lub 3 - dla której cachowanie trzeba by dopisac)

czy może są lepsze metody?

[cojack]

Jeżeli Ci się chce to dopisz ten cache, jeżeli nie, to 1 sposób i nie masz się nad czym zastanawiać.

[ChrisB]

nie mam się co przejmować że robi mi full table scana i nie korzysta z kluczy? tak na poważnie? a co jak będę miał 100.000 rekordów tam lub i więcej

[cojack]

2 sposób nie robi nic innego, albo w mysql wyszukaj jakiś tablic konfiguracyjnych które przetrzymują pewne informacje o tabelach. I tam może znajdziesz ostatni dodany rekord.

[nospor]

Po pierwsze: skoro chcesz pobrać ostatni rekord to w pierwszym wariancie nie ASC a DESC
Po drugie: pierwszy wariant jest ok. Czasami gdy jest mało rekordów to dla mysql jest szybciej przelecieć po nich niż bawić się w indeksy. Sam często widzę coś takiego. Gdy liczba rekordów wzrasta to mysql korzysta już z indeksów.

[ChrisB]

to już taki detal:P tak na marginesie wyciagajac pierwszy rekord skanuje cala tablice - tez masakra;/

ale przy desc jest jeszcze gorzej

[SQL] pobierz, plaintext 
mysql> EXPLAIN SELECT * FROM statydzienne ORDER BY ids DESC LIMIT 1;
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | TABLE        | type  | possible_keys | KEY     | key_len | ref  | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | statydzienne | INDEX | NULL          | PRIMARY | 4       | NULL |  974 |       |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

tutaj zapytanie na tablicy z ~50k rekordów - nadal chce skanowac wszystkie (klucz jest na id_uzyt - primary, tablice posortowalem / zoptymalizowalem/zdefragmentowalem przed wykonaniem tego zapytania

[SQL] pobierz, plaintext 
mysql> EXPLAIN SELECT id_uzyt FROM uzytkownik ORDER BY id_uzyt DESC LIMIT 1;
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | TABLE      | type  | possible_keys | KEY     | key_len | ref  | rows  | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | uzytkownik | INDEX | NULL          | PRIMARY | 3       | NULL | 47012 | USING INDEX |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

no i 600k dalej przynajmniej w przypadku explain - nie potrafi posluzyc sie indexem

[SQL] pobierz, plaintext 
mysql> EXPLAIN SELECT id FROM historia ORDER BY id ASC LIMIT 1;
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | TABLE    | type  | possible_keys | KEY     | key_len | ref  | rows   | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | historia | INDEX | NULL          | PRIMARY | 4       | NULL | 682851 | USING INDEX |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
1 row IN SET (0.00 sec)
[SQL] pobierz, plaintext 

wrzucajac po select cos innego niz tylko id_uzyt/id - znika z ostatniej kolumny "using index"


PS
edycja tego postu i jego zapis = totalny rozwał...



Ten post edytował ChrisB 30.08.2010, 09:52:36

[nospor]

Kurde, dziwne... No mam indentczyną sytuację, z tym ze, w rows wpisuje mi 1 Wszystko inne jest dokładnie takie samo. :/

[ChrisB]

może coś źle robię z indexami... już ogolnie ręce mi przy tym explain opadają

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `historia` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_uzyt` mediumint(9) NOT NULL DEFAULT '0',
  `grupa` smallint(6) NOT NULL,
  `data` date DEFAULT NULL,
  `wartosc_portfela` double(14,2) NOT NULL DEFAULT '0.00',
  `akcje` double(14,2) NOT NULL DEFAULT '0.00',
  `depozyt` double(14,2) NOT NULL DEFAULT '0.00',
  `miejsce` smallint(6) NOT NULL DEFAULT '0',
  `gotowka` double(14,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY  (`id`),
  KEY `id_uzyt` (`id_uzyt`),
  KEY `data` (`data`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=682916 ;
[SQL] pobierz, plaintext 

pełna struktura tablicy


i tu jeszcze mam inne pytanie - zakładając że NIGDY nie będę chciał wybierać rekordów z tej tablicy po id - powinienem go usunąć i zostawić tablicę bez klucza głównego? (wybieram dane tylko po dacie lub id_uzyt)

[nospor]

struktura jest ok. Wstawiłem ją do siebie i lata po jednym rekordzie. Baze masz jakąś zrąbaną

i tu jeszcze mam inne pytanie - zakładając że NIGDY nie będę chciał wybierać rekordów z tej tablicy po id - powinienem go usunąć i zostawić tablicę bez klucza głównego? (wybieram dane tylko po dacie lub id_uzyt)

Klucz główny powinien być zawsze. Może to być ID, moze to być klucz składający się z kilku pól, ale ich zestawienie ma dawać unikalność rekordu

[ChrisB]

i nie jest to jedyny problem z indexami jaki mam;/ przy wybieraniu danych z where po indexie - w paru innych przypadkach tez skanuje wszystko jak leci;/
ten w tym temacie najczęściej występuje i jest najprostszy dlatego chciałem od niego zacząć

to stoi na vps z mysql 5.0.67 bodajże, z własnymi ustawieniami my.cnf - może coś tam mam źle...
zauważyłem też naprawdę spore wartości
Handler_read_rnd_next
(Liczba żądań odczytu następnego rekord w pliku z danymi. Wartość jest duża przy wykonywania wielu przeszukiwań tabeli. Ogólnie sugeruje to, że tabele nie są poprawnie zindeksowane lub że zapytania nie są napisane w sposób pozwalający skorzystać z istniejących indeksów.)


co do indexów
czyli zakładając że wybieram przewaznie dane z tej tablicy w formie

where id_uzyt=XX and data >'yy'

powinienem złożony klucz na id_uzyt,data jako unique nałożyc? (nie ma id_uzyt i daty w tej kombinacji powtarzającej się) i usunąć klucz primary auto increment z ID i całe pole wywalić?
i czy ten index będzie też wykorzystywany przy zapytaniach tylko na id_uzyt lub tylko na date - czy powinienem założyć 3 - id_uzyt/data(unique), id_uzyt(index), data(index)

Ten post edytował ChrisB 30.08.2010, 10:10:12

[nospor]

Powiem jak ja to robię:
klucz główny w postaci ID staram sie jednak zawsze dla tabeli zachować. No chyba, że naprawdę, naprawdę nie jest mi to potrzebne, wówczas klucz główny robię z innych pól. Zdarzyło mi się to tylko parę razy

powinienem złożony klucz na id_uzyt,data jako unique nałożyc? (nie ma id_uzyt i daty w tej kombinacji powtarzającej się) i usunąć klucz primary auto increment z ID i całe pole wywalić?

jeżeli faktycznie te dwa pola dokładnie identyfikują rekord, to możesz ID wywalić. A z tych dwóch masz zrobić PRIMARY KEY a nie uniq - uniq będzie w domysle PRIMARY.
Pamiętaj tylko, że jak będziesz kiedyś chciał napisać panel do zarządzania historią, to usuwanie rekordu będzie polegać na podaniu dwóch wartości zamiast jednej ID.

[ChrisB]

jak usuwam to albo dla danego użytkownika (po id_uzyt) albo dla danego dnia (po dacie) więc ten id nie jest mi potrzebny - właściwie to dodałem go parę dni temu licząc że coś to polepszy właśnie dostęp do tej tabeli ale żadnego pozytywnego skutku nie zauważyłem
wieczorem jeszcze spróbuje go usunąć i dać primary po id_uzyt/dacie i zobaczę jakie to da efekty

a wracając do głównego wątku - mój problem z brakiem użycia indexów może być spowodowany nieprawidłowymi ustawieniami w my.cnf? jak coś to mogę je potem wkleić całe...

[cojack]

Klucz główny powinien być zawsze.

g. prawda.

[nospor]

@cojak nadwyraz rzeczowa argumentacja
Wiem, że bez klucza głównego tabela może "żyć". Jednak dla własnego dobra, nawet jesli w danej chwili go nie widzisz, dobrze jest by klucz główny był.

[cojack]

Złączenia naturalne:

http://bobo.fuw.edu.pl/DB/OLD/wyklad5.html

[nospor]

@cojak nie wiem co chciałeś pokazać tym linkiem, ale jeśli chciałeś go użyć jako potwierdzenie swojej "super argumentacji" to nie wiem o co ci chodziło. W linku znalazłem taki tekst:

: każda tabela musi posiadać klucz główny, a wartości klucza głównego muszą być w ramach tabeli unikalne i nie równe NULL. W szczególności, zapobiega to wystąpieniu w tabeli powtórzeń wierszy.

Może zaznacz tekst. o który tobie chodziło, bo naprawdę nie chce mi się tego czytać.

[cojack]

Nospor no przecież wytłuściłem Ci o czym masz czytać, złączenia naturalne poszukaj. I tabela równie dobrze może posiadać parę jako klucz główny, nie musi posiadać w cale klucza głównego, zależy co implementujesz.

Ten post edytował cojack 31.08.2010, 08:18:31

[nospor]

I tabela równie dobrze może posiadać parę jako klucz główny,

A gdzie ja napisałem że nie może?

A pozatym ty mi napisałes "g.prawda" do odpowiedzi:

Klucz główny powinien być zawsze.

Para to też klucz główny, wiec klucz istnieje. Sam to teraz mowisz, ze para moze byc kluczem głównym, więc sam sobie przeczysz słowami "g.prawda".

[cojack]

Ja tylko stwierdzam że nie musi być klucza głównego jak nie jest potrzebny.

[nospor]

Ja tylko stwierdzam że nie musi być klucza głównego jak nie jest potrzebny.

To jest tylko ładniej napisane "g.prawda"
Zacytuję ci odpowiedż jaką ci dałem na "g.prawda"

Wiem, że bez klucza głównego tabela może "żyć". Jednak dla własnego dobra, nawet jesli w danej chwili go nie widzisz, dobrze jest by klucz główny był.

I mówię ci to na podstawie zawodowego doświadczenia Też czasem nie używam klucza głównego, zdarza się to sporadycznie i tylko wówczas gdy naprawdę, naprawdę, naprawdę nie jest konieczne

edit: po przeczytaniu tego wszystkiego jeszcze raz po części się z tobą zgodzę. Źle to napisałem
Powinienem był napisać "W większości przypadków powinien być zawsze" (tak miałem w domyśle - mój błąd).Większość używanych przez nas tabel powinna mieć klucz główny. Oczywiście zdarzają się odstępstwa gdzie ten klucz niczemu służyć nie będzie.
Twoja cudna argumentacja "g.prawda" niestety zadziałała na mnie jak płachta na byka. Twoje ostatnie zdanie już jest "lepiej" sformułowane i "zmusiło" mnie do ponownego przetrawienia treści