Mysql – How to find MySQL users causing high load

User accounts are again stored in tables in terms of rows and columns and also its respected permissions under mysql database wherein it has primary key as host and username. So, I don't think so you have any limitations. But you have something called max_connections that has to be set to a value, say it has 100000 as max that you can set. Then keeping each user connection in mind then we can go upto 99999 users connected to it parallely.

My question to you is.

1. Why do you need all different users to be connected to the mysql connection pool?
2. It is highly impossible to make a note of users permissions and its passwords.
3. If you need those many users for application purpose, you may try doing it in regular innodb tables and have list of users under one group and tag them to a role. Just grant multiple roles to the users who needs the access.

Because it is missing, I would suppose that your query execution plan shows a full-scan on your raw-data table. If this is the case, then your entire rows will be loaded into memory (including raw columns), which obviously is not ok.

The easiest way to avoid this would be to create an index on the filtering columns, so that the query will scan the index prior, and only if the row is a candidate for your result-set, will reach the table and load it into memory.

From comment discussion:

The query performs fast mostly because of the index (thus the slow performance when removing it) ...but your rows, are still loaded entirely. As you thought, only some of the rows get into the memory, but this does not mean that only parts of them are loaded. The trick here is that the index points only to those rows that are relevant. You're using the index with a ">" operator, which causes a Range Scan, so it is possible that more rows than you need/get are referenced.

You could try to apply the 18 limit earlier, rather at the end of the query. Or you could make a new table with only the raw column and a foreign key to the first table. Link the result from the first table just at the very end of the query. Remember though, if you want to order the rows, and perform the ORDER BY after you join the two tables, it will still perform the sorting with the raw data loaded into memory.

Try to sort the rows of the main table before joining the raw table. If the main table is leading the join, then you should get the rows sorted after the join too.