Re: querying time is too high

[Anthony Willard]

You index has date as the primary component of the index, while it helps in selecting data it is of little help in the grouping operation.

You said that you tried an index - what keys? username,id?

You could try swapping the keys in the PRI to username,id,date. That's how I would create that key. But, that's also not knowing the data.

If you have that many data rows and you are using a dynamic format (you have varchar() columns), you can garner some improvment by converting your varchar() columns to char(). Yes, it will increase the space consumed, but then the records are all the same length and can improve the query process.

You may gain some significant improvement if you were to vertically partition your table. That is, put your "lesser" columns in a different table, away from the main data. That way your 16.5 M rows will occupy less space (the main columns, anyway) and therefore can be accessed more quickly. This will create overhead on the inserts, but will pay off on the back end - reporting.