przepełniająca tabela bazy danych Opcje

[maksik]

Witam

Mam dwie tabele powiedzmy że to są X, Y
X- znajdują się w tej tabeli artykuły do oglądania przez użytkowników, po kolei wyświetla użytkownikowi każdy rekord, gdy użytkownik obejży artykuł zapisuje się do bazy Y numer id artykułu i następnie gdy użytkownik przechodzi do następnego artykułu, wtedy skrypt sprawdza w tabeli Y który artykuł już się wyświetlił (aby nie oglądał tego samego po kolei) i pobiera z tabeli X pierwszy artykuł do wyświetlenia którego id nie jest zapisane w tabeli Y i tak wkółko.


Problem polega na tym że tabela Y się przepełnia tzn gdy uzyska kilkadziesiąt tysiecy rekordków należy ją czyścić ponieważ skrypt wyświetlania wolno zaczyna wczytywać. Jest możliwość innego rozwiązania, lub można temu jakoś zaradzić aby nie trzeba było tego czyścić?

[toffiak]

Kilkadziesiąt tysięcy rekordów to tyle co nic dla każdej bazy danych, problem leży prawdopodobie gdzie indziej.

[maksik]

a samo to, że skrypt przed wyświetleniem wyniku za każdym razem przeszukuje te ogromną tabele z id było już wyświetlane? to też nie ma wpływu?

[phpion]

Problem pewnie leży w braku/nieprawidłowych indeksach lub w zapytaniu pobierającym dane z tabeli Y. Bez struktury tabeli i zapytania nic nie poradzimy.

W sumie: pewnie możesz to oprzeć na 1 zapytaniu z wykorzystaniem NOT EXISTS.

[maksik]

samo zapytanie pobierające artykuł i sprawdzające czy nie był on już wcześniej wyświetlany, możecie określić czy powinien on być inaczej skonstruowany i to on może powodować spowalnianie przy dużej ilości rekordów?

[PHP] pobierz, plaintext 
 $query = mysql_query("SELECT X.id, nazwa, wpis FROM X WHERE NOT EXISTS (Select id FROM Y WHERE X.id = Y.id and login = '".$login."')  LIMIT 0,1  ");
 
[PHP] pobierz, plaintext 

struktura tabeli X

[PHP] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `X` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nazwa` varchar(99) CHARACTER SET latin2 DEFAULT NULL,
  `limit` int(11) DEFAULT NULL,
  `login` varchar(30) CHARACTER SET latin2 DEFAULT NULL,
  `date` varchar(50) CHARACTER SET latin2 DEFAULT NULL,
  `wpis` varchar(200) COLLATE utf8_polish_ci NOT NULL,
  `licznik` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=42815 ;
[PHP] pobierz, plaintext 

tabeli Y

[PHP] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `Y` (
  `idY` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(50) CHARACTER SET latin2 DEFAULT NULL,
  `login` varchar(30) CHARACTER SET latin2 DEFAULT NULL,
  `date` varchar(50) CHARACTER SET latin2 DEFAULT NULL,
  `akcja` int(2) NOT NULL,
  PRIMARY KEY (`idY`),
  KEY `Indeks6` (`login`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=2605241 ;
[PHP] pobierz, plaintext 

Ten post edytował maksik 17.12.2013, 10:06:37

[phpion]

WHERE X.id = Y.id and login = '".$login."'

Zmień typ kolumny Y.id na taki by odpowiadał X.id. Aktualnie masz varchar vs integer, czyli podczas przeszukiwania danych następuje rzutowanie wartości. Dodatkowo załóż jeden indeks na obu kolumnach (id oraz login). Aktualnie masz tylko na login. Powinno znacząco pomóc.

[maksik]

czyli index dla tabeli Y powinny wyglądać tak?

[PHP] pobierz, plaintext 
 PRIMARY KEY `Indeks6` (`idY`),
KEY `Indeks6` (`login`)
[PHP] pobierz, plaintext 

Ten post edytował maksik 17.12.2013, 10:21:58

[phpion]

Nie, napisałem Ci pogrubioną czcionką: jeden indeks na obu kolumnach. Wykonaj to:

[SQL] pobierz, plaintext 
ALTER TABLE `Y` CHANGE COLUMN `id` `id` INT(11) NOT NULL  
, DROP INDEX `Indeks6` 
, ADD INDEX `Indeks6` (`id` ASC, `login` ASC) ;
[SQL] pobierz, plaintext 

Sprawdź czy zapytanie przyspieszyło. Jeśli nie - wklej wyniki:

[SQL] pobierz, plaintext 
EXPLAIN SELECT ...
[SQL] pobierz, plaintext 

gdzie ... to całe Twoje zapytanie.

[nospor]

X- znajdują się w tej tabeli artykuły do oglądania przez użytkowników, po kolei wyświetla użytkownikowi każdy rekord, gdy użytkownik obejży artykuł zapisuje się do bazy Y numer id artykułu i następnie gdy użytkownik przechodzi do następnego artykułu, wtedy skrypt sprawdza w tabeli Y który artykuł już się wyświetlił (aby nie oglądał tego samego po kolei) i pobiera z tabeli X pierwszy artykuł do wyświetlenia którego id nie jest zapisane w tabeli Y i tak wkółko.

Skoro artykuly wyswietlane są pokolei, to po co zapisywac calą sciezke jaką przeszeedl? Zapisuj tylko ostatnio obejrzany art, a nastepnie wyswietlaj art z wiekszym ID od ostatnio obejrzanego.

Ponadto w tabelach powinien byc zapisywane ID uzytkownika a nie jego login.

[maksik]

Skoro artykuly wyswietlane są pokolei, to po co zapisywac calą sciezke jaką przeszeedl? Zapisuj tylko ostatnio obejrzany art, a nastepnie wyswietlaj art z wiekszym ID od ostatnio obejrzanego.

Ponadto w tabelach powinien byc zapisywane ID uzytkownika a nie jego login.

to by było dobre rozwiązanie jednak ten układ "ilościowy" jest istotny dla zliczania statystyki dniowej dla poszczególnego użytkownika

[phpion]

Dałeś "Pomógł" - rozumiem, że zapytanie przyspieszyło

[maksik]

Wpisując zapytanie ze starymi indeksami

[PHP] pobierz, plaintext 
 Showing rows 0 - 0 (1 total, Wykonanie zapytania trwało 0.3033 sekund(y))
[PHP] pobierz, plaintext 

z tymi które zasugerowałeś

[PHP] pobierz, plaintext 
 Showing rows 0 - 0 (1 total, Wykonanie zapytania trwało 0.4003 sekund(y))
[PHP] pobierz, plaintext 

lub

[PHP] pobierz, plaintext 
Showing rows 0 - 0 (1 total, Wykonanie zapytania trwało 0.0003 sekund(y))
[PHP] pobierz, plaintext 

Pomogło bardzo, ale nie wiem dlaczego chociaż na kilkadziesiąt prób rzadko trwa to 0.4 s , a raz ułamek sekundy!

Mam jeszcze pytanie do ciebie ponieważ kiedyś miałem problem u usługodawcy vps który notował zbyt duże obciążenia kodu php, zawsze pomagało gdy oczyścilem tabele Y czy te indeksy mogły być tego przyczyną?

Ten post edytował maksik 17.12.2013, 11:30:02

[phpion]

Tak, jeżeli uruchamiałeś zapytania trwające stosunkowo długo to obciążałeś bazę danych. Im szybciej wykonywane zapytania tym mniejszy narzut. Kilkadziesiąt tysięcy rekordów to dla bazy pierdnięcie o ile mają odpowiednie typy danych, indeksy.

[CuteOne]

Rozwiązanie to mix odpowiedzi nospora i phpiona. Stwórz tabelę archiwalną, w której będziesz jedynie dopisywał kolejne rekordy oraz tabelę taką jak zaproponował nospor. W drugiej tabeli będzie tylko jeden rekord na użytkownika co powinno znacząco pomóc w odczycie danych

[maksik]

jednak nic to nie dało... przeciążenie się pojawiło, a sposób nospora się nie sprawdzi w moim przypadku ponieważ zapytania do "większej" tabeli z bazy danych również muszą być wywoływane za każdym artykułem do liczników także chyba nie ma sensu oddzielenia tego zapytania w innej tabeli? może te zapytanie powoduje problem skoro indexowanie jest wporządku? a czy tabela X nie musi mieć również jakiś indexów?

[CuteOne]

Zamiast dodać kolumnę "licznik" do tabeli z danymi artykułu, zliczasz ilość wejść COUNT'em?

[maksik]

dokładnie tak, COUNT może wywoływać nadmierne obciążenia?

[nospor]

No ale po co ci tu count?? W mojej wersji miales zapisywac tylko ID ostatnio ogladanego rekordu dla danego usera i tu zaden count nie jest potrzebny.
W mixie zas, gdzie miales dodac tabele archiwalną to tam jest tez tylko INSERT wiec COUNT nadal nigdzie nie jest potrzebny.
W mixie masz lepsza sytuacje, bo do duzej tabeli idziesz tylko raz wraz z insertem a nie jak teraz dwa razy do odczytu ostatniego id i do inserta.

Zas co do indeksu w X to chyba juz nie trzeba, bo do arta przeciez odwolujesz sie przez ID a ono jest kluczem glownym, czyz nie?

[maksik]

tak COUNT używam do zliczania artykułów obejżanych przez użytkownika i następnie do ustawień statystyki dla każdego z użytkownika która jest wyświetlana zawsze po każdym obejżeniu dlatego jeżeli powoduje przeciążenie to w tabeli archiwalnej chyba te obciążenie będzie dalej występować?

[nospor]

Ciezko cos ogarnac z tych opisow twoich....
COUNT samo w sobie jest bardzo szybkie nawet dla bardzo duzej liczby rekordow.

1) Sprawdz czas wszystkich zapytan jakie generujesz, to bedziesz wiedzial ktore dokladnie ci muli. Tu nie ma co zgadywac.
2) Powinienes napisac poprawnie swoją tabele. Pisalem ci juz o tym wczesniej, przypomne jeszcze raz:
`login` varchar(30) CHARACTER SET latin2 DEFAULT NULL,
`date` varchar(50) CHARACTER SET latin2 DEFAULT NULL,
`akcja` int(2) NOT NULL,

Nie powinno byc pola login, tylko id usera
Data powinna być polem datowym a nie tekstem. Wkoncu data to data.
akcja powinna byc tinyint a nie int, bo zakladam ze tam są male liczby.


To tak na poczatek.