Mysql Queries in big data table
Multi tool use
I have problem with my mysql database table. I have more then 20 millions rows in table. Table structure shown below. Main problem is that queries take really long time to execute (some queries take more then 20 second). I use indexes where i can, however many queries use date range and with date range my indexes don't work. Also in queries i use almost every column. What i need to change to my data table, to improve efficiency?
Some of my queries:
Your first query is better written as:
Why?
Then, for this query, a reasonable index is history(action, place, created_at, deleted_at).
So, I would start with multi-column indexes.
If you continue to have performance issues, you should then consider partitioning the data based on the created_at date.
INDEX(a), INDEX(b) serves some purposes, but the "composite" INDEX(a,b) better serves some queries.
Needs
I prefer to write the date range thus:
It's a lot easier than dealing with leap year, end of year, etc. And it works 'correctly' for DATE, DATETIME, DATETIME(6), TIMESTAMP, and TIMESTAMP(6).
For this
I would try this as the most likely:
Do not have separate tables for separate years. If you will be deleting old data, then consider PARTITION BY RANGE(TO_DAYS(...)) in order to get the speed of DROP PARTITION. (But that is another discussion.)
If I was in your situation I would consider a paged database name. By this I mean have multiple history_X tables where X is an int related to the content.
Since this is a history table is it possible to include part of the date in the name?
You said that you use ranges to search for the data, so if you were to use year in the table name you could have
Then you could search with the table that applies to your date range.
If you need date from a range that spans to tables then you could use a UNION query to bridge the 2 result sets into one.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
