0

I have a MySQL table with 20 million rows. I am using the average function and this is taking too much time.

I want to break this big table up into multiple smaller tables. I want to boost speed and user experience by allowing the user to search only part of the data.

If possible, I would like to do this using a single SQL statement. The big table is in the following format:

Column    Column   Column   Sector

data      data     data     Retail
data      data     data     Utility
data      data     data     Retail
data      data     data     Insurance
data      data     data     Retail
data      data     data     Agriculture
data      data     data     Agriculture
data      data     data     Retail

This big table would be broken up according to the value in the Sector column. There would be a Retail table, an Agriculture table and so on.

Ned Hulton
  • 477
  • 3
  • 12
  • 27
  • 2
    Why not fix the problem instead of moving it around? Have you got the correct indexes? – Ed Heal Jun 14 '16 at 00:11
  • @EdHeal The problem is that the table has more than one hundred columns and the SQL queries generated by the user (through PHP) differ each time, so indexing may not really help. – Ned Hulton Jun 14 '16 at 00:13
  • 1
    I think the problem is with your design. The solution that you propose will not fix this in the long term. You have heard of third normal form? – Ed Heal Jun 14 '16 at 00:15
  • You are probably looking for partitioning. That will work well if your queries touch a subset of rows. – Gordon Linoff Jun 14 '16 at 00:19
  • @EdHeal I came across database normalization on Google before posting this question. I am not sure if that is the right solution for me, but if you think it is then please explain. – Ned Hulton Jun 14 '16 at 00:21
  • @GordonLinoff Hi Gordon, I believe that might be the right solution for me, please provide more details. – Ned Hulton Jun 14 '16 at 00:22
  • Normalization is the best for databases for lots of reasons. Helps with performance. You will have to do this yourself because it is quite a bit of work – Ed Heal Jun 14 '16 at 00:23
  • Perhaps I can craft some solution. Always around the [Campaigns](http://chat.stackoverflow.com/rooms/95290) room if you want to ping me there. – Drew Jun 14 '16 at 04:14
  • @Drew Hi Drew, I have decided to partition the table. I was wondering if perhaps you could help me out with this new problem. http://stackoverflow.com/questions/37818604/partition-mysql-table-by-column-value Its not getting a lot of action. – Ned Hulton Jun 14 '16 at 18:06
  • I will try to ping [Rick James](http://stackoverflow.com/users/1766831/rick-james) . Read his stuff off his link on his profile page. It's a niche area. – Drew Jun 14 '16 at 18:10
  • @NedHulton if the queries are so varied that adding an index on Sector will not help, partitioning by sector _can_ make things even worse. – Uueerdo Jun 14 '16 at 18:42
  • Provide 3 different, but 'typical', sample queries. It may be possible to devise a plan for tackling your puzzle. – Rick James Jun 15 '16 at 07:55
  • 1
    "Multiple tables" is probably worse than `PARTITIONing`, which is probably worse than a 'good' `INDEX`. – Rick James Jun 15 '16 at 07:56

0 Answers0