0

I have a big database which contains alot of data. How can I check the size in bytes of all my results from a sql query?

I was not able to find a command/function.

EDIT: This is not a duplicate since I ask a whole other question. I need to find out how much storage space all the records returned by a select statement takes, I am not interested in the size of tables.

Black
  • 18,150
  • 39
  • 158
  • 271
  • Do you want to check the size of the result of a SELECT statement or do you want to check the size of the tables you have in your database? – Progman Jan 03 '18 at 11:34
  • Yes, the result of a `SELECT` statement. – Black Jan 03 '18 at 11:34
  • This is unusual, why do you want to know how big the result set of a SELECT statement is? Where do you need this information? – Progman Jan 03 '18 at 11:41
  • Because I need to see how much data a specific client stores so I can take further actions if he uses too much storage. I also want to create graphs in the backend to see which client is consuming the most storage etc. – Black Jan 03 '18 at 12:01
  • ;...so check the size of the objects in the database, not whatever diluted representation you'll get from some `select`. – underscore_d Jan 03 '18 at 12:05
  • Can you give an example? – Black Jan 03 '18 at 12:12
  • Possible duplicate of [How to get the sizes of the tables of a mysql database?](https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database) – underscore_d Jan 03 '18 at 12:14
  • @Black Your comment suggest you want to read the size of the storage of the tables but your also state you want to know the size of the result of a `SELECT` statement. Do you really want to know the size of the result of a `SELECT` statement or do you want to know the size of the tables stored in the MySQL database? – Progman Jan 03 '18 at 12:15
  • @underscore_d It seems like you did not understand the question. I do not search for a solution how to get the sizes of tables, I search for a solution how I can get the size of the result of a sql query e.g. `SELECT`. – Black Jan 03 '18 at 12:15
  • @Progman, I need the size of the result of a `SELECT` statement as I mentioned above... – Black Jan 03 '18 at 12:16
  • @Black I understand the question just fine; I just think it's nonsensical. What is 'the size of a `select` statement'? What you're really saying is presumably 'get me the number of bytes used by all rows in these tables where this predicate is true', but that's different from what a `select` statement returns, as omitting columns in the `select` would hide the fact that they still occupy space in the database, so even if there were a way to get the size of the `select` result set, you'd be underestimating the space needed to store the data rows underlying it, and so on. – underscore_d Jan 03 '18 at 12:18
  • @underscore_d I need to find out how much storage space in bytes the summary of all the records returned by a sql select statement takes. I hope that was clear enough. – Black Jan 03 '18 at 12:20
  • @Black What is "the summary of all the records"? Does that meant you want to compose a list of all tables that are ultimately referenced by the query and any views, etc. that it uses, then add the sizes of all rows from those tables that satisfy your `join`s and `where` and etc.? – underscore_d Jan 03 '18 at 12:22
  • @underscore_d "What is "the summary of all the records"? " I mean all records returned by a `SELECT`... – Black Jan 03 '18 at 12:29
  • Resultsets are stored in memory, so they do not use "storage" (= disk space) unless for temporary swap space (if you do not have enough ram). So it is a bit unclear what you are asking and it would help if you could explain in a bit more detail what number you are looking for exactly (e.g. if you want to somehow estimate which portion of a table disk space a user uses, or how much ram or traffic he requires; also if it has to be known globally (by "the admin") or by the client (who could just sum it up)). Currently, we seem to just be guessing what you are looking for. – Solarflare Jan 03 '18 at 12:51
  • I search the amount of storage which the records delivered by the `select` statement take, I mean physically on the HDD of the database server not on the RAM. – Black Jan 03 '18 at 15:08
  • something like... (A) identify all tables ultimately used, (B) get size of all columns in each A, (C) identify which rows are kept per joins/filters, (D) multiply sum of B by count of C per table, (E) get size of all indexes in As, (F) sum all D+Es. I doubt this is something folk often do for arbitrary `select`s that there's a ready-made way; I think typically they would just want the size of a single whole table or subset of rows therein. But anyway, maybe you can get started with that & eg this: https://stackoverflow.com/questions/4524019/how-to-get-the-byte-size-of-resultset-in-an-sql-query – underscore_d Jan 03 '18 at 21:15

1 Answers1

0

You might get this information by reading the sys.memory_by_user_by_current_bytes view. Also check 8.12.4.1 How MySQL Uses Memory on how to read the memory usage of MySQL, there are some queries you can use.

Progman
  • 16,827
  • 6
  • 33
  • 48
  • 1
    Doesn't this return the memory used by the user's session, transactions, temp tables, etc. - not of a given set of data, as the OP requested? – underscore_d Jan 03 '18 at 12:24