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ć?

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

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?

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.

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 

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.

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

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

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.

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

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

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ą?

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.

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

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?

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

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

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?

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ć?

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.

Utworzyłem drugą tabele według zaleceń co prawda przy tej samej ilości rekordów waży ona 2x mniej, ale czas zapytania tej bazy trwa 5x dłużej, może zapytanie powinno wyglądać inaczej? zmieniło się w zapytaniu to, że wyszukuje rekordy użytkownika nie po loginie, a jego numerze id

pokaż wynik explaina
czyli explain select ....

dla zapytania explain zwraca mi taki wynik

dla starej tabeli Y

[PHP] pobierz, plaintext 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	PRIMARY 	X 	ALL 	NULL	NULL	NULL	NULL	6059 	Using where
2 	DEPENDENT SUBQUERY 	Y 	ref 	Indeks6 	Indeks6 	33 	const 	1 	Using where
[PHP] pobierz, plaintext 

dla nowej tabeli Y

[PHP] pobierz, plaintext 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	PRIMARY 	X 	ALL 	NULL	NULL	NULL	NULL	6059 	Using where
2 	DEPENDENT SUBQUERY 	Y2 	ref 	Indeks6 	Indeks6 	4 	const 	3 	Using where
[PHP] pobierz, plaintext 

sprawdź czy dla nowej tabeli Y nie masz zduplikowanych danych, dlatego że zostały pobrane 3 rekordy, a w starej wersji tylko jeden
po drugie NOT EXISTS nie wykorzystuje indeksów co sprawia że całe zapytanie jest wolniejsze, spróbuj je zastąpić przez NOT IN x.ID(SELECT ...).

Zauważ że z tabeli X zostało pobrane 6059 rekordów,
możesz spróbować ograniczyć tą ilość. Skoro szukany następny wpis to można założyć że ma on większe ID, więc możesz dodać WHERE x.ID>obocne_id AND x.ID NOT IN(...)

po dokonaniu zmian zobacz wynik EXPLAIN czy coś się poprawiło

czy zamiast tego zapytania

[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 

zastąpić je na takie które wyszukuje id ostatniego artykułu, a następnie wyświetla następny o większym numerze od poprzedniego?
Czy to drugie rozwiązanie jest bardziej optymalne?

[PHP] pobierz, plaintext 
$query = mysql_query("SELECT id FROM Y WHERE login = '".$login."' ORDER BY id DESC LIMIT 0,1");
$query = mysql_Fetch_array($query);
$numerid = $query['id'];
 
$query = mysql_query("SELECT X.id, X.nazwa, X.wpis FROM X WHERE id > '".$numerid."' ORDER BY id LIMIT 0,1");
[PHP] pobierz, plaintext 

spróbuj np. tak

[SQL] pobierz, plaintext 
SELECT X.id, nazwa, wpis FROM X WHERE X.ID>'".$numerid."' AND X.ID> (SELECT max(Y.id) FROM Y WHERE Y.ID>'".$numerid."' AND login = '".$login."')  LIMIT 0,1
 
[SQL] pobierz, plaintext 

pamiętaj aby zawsze sprawdzać przez explain co jest bardziej wydajne

Nie mogę chyba tego zastosować w jednym zapytaniu ponieważ parametr $numerid znajduję za pomocą pierwszego zapytania.

Na które parametry w wynikach explain muszę zwracać szczególną uwagę?

wszystkie

przykładowo zobacz na twoje ostatnie wyniki
w pierwszym w kolumnie rows miałeś 6000 z jeden tabeli i 1 z drugiej czyli baza musiała "ręcznie" porównać 6000
w drugim przypadku miałeś 6000 i 3 czyli trzeba było ręcznie porównać 6000*3 rekordów

dalej w type masz "all" co oznacza że nie udało się skorzystać z żadnego indeksu i baza musiała analizować wszystkie rekordy w bazie.

Skoro nie masz nigdzie zapisanego id ostatniego artykułu to pomiń ten warunek(X.ID>'".$numerid."') w takim przypadku niema sensu dwa razy pobierać prawie tego samego

ogólnie polecam poczytać tutaj http://blog.ksiazek.info/2010/04/09/benchmark-i-profiling/ oczywiście nie tylko ten wpis