Subscribe via rss
Guest
or Ask a Question
Discussions
Add a reply View Replies (9)

size of muscol_statistics

Music Collection
Saturday, April 05 2014, 11:52 AM
Salman Pannu
Salman Pannu
Offline
0
the size of muscol_statistics in mysql database has increased so much that the database size has gone upto 1.25 gb how to reduce it and why is it taking up so much space in mysql

Accepted Answer

Germinal Camps
Germinal Camps
Offline
Thursday, April 10 2014, 02:44 PM - #Permalink
0
You just need to execute something like:

DELETE FROM #__muscol_statistics WHERE date_event <= "2014-02-01"


for example. that will erase all data prior to February 1st, 2014. you can do it with the date you want to. remember to change the #__ for your actual table prefix.

do you know what I mean?
The reply is currently minimized Show
Responses (8)

  • Accepted Answer

    Germinal Camps
    Germinal Camps
    Offline
    Saturday, April 05 2014, 02:18 PM - #Permalink
    0
    this DB table needs to be cleaned from time to time, specially if you're keeping it from long time ago.
    what I suggest is to delete rows older than, for example, 2 or 3 months.
    The reply is currently minimized Show

  • Accepted Answer

    Salman Pannu
    Salman Pannu
    Offline
    Tuesday, April 08 2014, 07:51 AM - #Permalink
    0
    its so much tedious job having to delete so many rows is it not possible to integrate a maintenance tool in music collection to take care of it in the future
    The reply is currently minimized Show

  • Accepted Answer

    Germinal Camps
    Germinal Camps
    Offline
    Tuesday, April 08 2014, 09:31 AM - #Permalink
    0
    yes we will add a toold to do maintenance, however the task is not actually very "tedious".. it can be done with just one MySQL sentence...
    I can help you with that, all you need to do is to DELETE all rows older than X months (that can be said to MySQL in just one statement)
    The reply is currently minimized Show

  • Accepted Answer

    Salman Pannu
    Salman Pannu
    Offline
    Tuesday, April 08 2014, 09:37 AM - #Permalink
    0
    yes pls that would b great can u messg me how to do that
    thanks in advance :)
    • Germinal Camps
      Germinal Camps
      more than a month ago
      sure. do you have access to your MySQL interface? phpMyAdmin maybe?
    The reply is currently minimized Show

  • Accepted Answer

    Salman Pannu
    Salman Pannu
    Offline
    Thursday, April 10 2014, 01:02 PM - #Permalink
    0
    yes i have full access to the mysql & phpmyadmin i have a dedicated server
    The reply is currently minimized Show

  • Accepted Answer

    Salman Pannu
    Salman Pannu
    Offline
    Thursday, April 10 2014, 03:12 PM - #Permalink
    0
    i get an error when i run the query

    Error
    SQL query:


    DELETE FROM #snzl_muscol_statistics WHERE date_event <= "2014-02-01"
    MySQL said: Documentation

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

    mysql version is 5.6
    The reply is currently minimized Show

  • Accepted Answer

    Germinal Camps
    Germinal Camps
    Offline
    Thursday, April 10 2014, 03:15 PM - #Permalink
    0
    the table is most certainly not called #snzl_muscol_statistics , it's called snzl_muscol_statistics
    The reply is currently minimized Show

  • Accepted Answer

    Salman Pannu
    Salman Pannu
    Offline
    Thursday, April 10 2014, 03:29 PM - #Permalink
    0
    oops i thought the # was part of command .thank you so much i have run it and worked :)
    The reply is currently minimized Show
Your Reply