|
In my case I thought on saving database space was to accomplish the following:
To remove all records from the different statistics pages where the jos_ariquizstatistics status is not Finished and the StartDate is older than 2 days. I am not interested in tests not completed older than 2 days old, and a lot of users or guests start the quizzes and decide it is not for them and quit, so I do not want those results.
The reason for the older than 2 days records then I do not interfere with current tests that might be taken. In my case I queried where the Status <> "Finished", but in your case you might want to replace that with UserId = 0
Then remember to optimize your tables to reduce the size.
Here's my code:
-----------------------------------------------------------------
--CHECK TABLE SIZES (do copy paste the results somewhere to compare afterwards)
-----------------------------------------------------------------
SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_name in('jos_ariquizstatisticsinfo' ,'jos_ariquizstatistics','jos_ariquizstatistics_pages') ORDER BY `Size in MB` DESC;
-----------------------------------------------------------------
--DELETE RECORDS
-----------------------------------------------------------------
DELETE
QSI, QS, QSP
FROM
jos_ariquizstatisticsinfo QSI,jos_ariquizstatistics QS,jos_ariquizstatistics_pages QSP
WHERE
QSI.StatisticsInfoId = QS.StatisticsInfoId AND QS.PageId = QSP.PageId
AND
(QSI.Status <> "Finished") and (QSI.StartDate < now() - interval 2 DAY);
------------------------------------------------------------------
--OPTIMIZE TABLES TO REDUCE SIZE
-----------------------------------------------------------------
optimize table jos_ariquizstatisticsinfo ,jos_ariquizstatistics,jos_ariquizstatistics_pages;
------------------------------------------------------------------
--SEE THE TABLE SIZES NOW
-----------------------------------------------------------------
SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_name in('jos_ariquizstatisticsinfo' ,'jos_ariquizstatistics','jos_ariquizstatistics_pages') ORDER BY `Size in MB` DESC;
AriSoft, do you agree?
Kind Regards
|
