njtynhj

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...

Please consider below query - I want to insert 2 record with same 'categoryId'. To genereate categoryId I am using mariadb sequence https://mariadb.com/kb/en/library/sequence-overview/. NextVal() return next sequence number and LastVal() return last sequence number (same as NextVal() function). Above query is working fine if INSERT statement order is left to right(I found in my test). Example: " insert into x values('1'), ('2') ". '1' will insert first then '2', But I am not sure mariadb will always follow this order or not. Please help me about INSERT order or suggest me some different query. 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.