MySQL partitioning or deleting older data?

user_3762004 0
Viewed 1810

I have a recent activities table on mySQL, its MyISAM and contains around 46million records. It has been indexed properly and all that is done. But some queries can still take a few seconds to perform on it.

So I was wondering, what would be the best method to increase query performance on this database. Should I partition the table or delete older data? I mainly use some calculations on the data from this month for the queries, the other usage is showing users their recent activities, as well as us having to manually monitor their activities from time to time.

I have been thinking to delete all data older than this year, which should delete around 24 million rows. (as its been 4 months since the start of this month, so data that old should not matter as much).

Or I can partition according to month, but I dont know how that would implement in laravel, will I have to change partitions everytime to get data older than the current months?

Edit: This 1 query is an important one, it is performed after a specific activity from a user and this specific query took 8s to perform, and it went through 40,000 rows, using the user_id index. What would help me in improving this query, is either if it used multiple index, because then the query would have been narrowed down to very very few rows. Or if i remove old data, then it will have less rows to go through to finish the calculation.

select  *
    from  `recent_activites`
    where  `id` > 20443580
      and  `user_id` = 20580
      and  `updated_at` > '2020-04-01 00:00:00'
      and  `type` in (?, ?, ?, ?, ?, ?, ?, ?, ?) ```

Edit: Query : This query took 8.72 ms to perform at 1 time, but always seems to take more than 1 ms Time is there because it decreases rows

enter image description here

These are the indexes enter image description here

laravel myisam mysql
2 Answers
  • When removing that much of a table, it is faster to copy over the rows you want to keep.
  • You should move from MyISAM to InnoDB.
  • PARTITIONing would help with future purging of old data.
  • Adding PARTITIONing will cost time. So will any technique for deleting old data. Which is more important? Minimizing blockage? Or speed of getting through with the task.
  • PARTITIONing, per se, won't speed up queries. However, you seem to need a "2-D" index.
  • You must re-think the indexes when going to partitioning.
  • A Summary table might improve performance significantly.

Several of the above steps can be done simultaneously -- switch to InnoDB, add partitioning, remove old data, change indexes. (Summary tables is a separate task.) I recommend experimenting before tackling your live system.

References:

user_17668310

You can move some data to the archive database, I mean store old data in separate database (archive) and actual data in you current database. In this case you will have still good performance for actual (fresh) data and possibility to access old data at the same time.

To handle this you need to setup 2 connections in your Laravel system - create new connection in config/database.php.

And now you should be able to access your archive data like this:

$archive_data = DB::connection('name_of_archive_connection')->table('table_name')->select('*')...
$actual_data = DB::connection('name_of_main_connection')->table('table_name')->select('*')...

Also you can easy move your data between databases just by insert/delete mysql functions like in example above.

user_64791530
Related