43

I'm trying to find out the exact size of one column within a table. PHPMyAdmin doesn't show size of columns, only the tables.

Any way I can get the size of the column?

Thankyou

user1446650
  • 1,197
  • 6
  • 15
  • 24

3 Answers3

55

If you want to find out the size of column=COLUMN_NAME from table=TABLE_NAME, you can always run a query like this:

SELECT sum(char_length(COLUMN_NAME)) 
FROM TABLE_NAME;

Size returned is in bytes. If you want it in kb, you could just divide it by 1024, like so:

SELECT sum(char_length(COLUMN_NAME))/1024 
FROM TABLE_NAME;
tim berspine
  • 825
  • 1
  • 9
  • 17
  • 1
    This certainly helped me out. I also found that by removing the 'sum' part of the query produced exactly the same result. SELECT char_length(COLUMN_NAME) FROM TABLE_NAME; – Alan N Feb 07 '18 at 17:15
  • 1
    `sum()` is necessary when we want add multiple columns string length – Exception Jul 20 '18 at 20:03
  • 7
    i don't think you are right here, char_length returns character count not the bytes – codebusta Nov 27 '19 at 07:36
  • 9
    I believe `CHAR_LENGTH()` is measured in characters so it depends on the encoding. `LENGTH()` is measured in Bytes. – Elliott B Aug 20 '20 at 21:14
  • As @ElliottB stated - `CHAR_LENGTH()` returns the number of characters, `LENGTH()` returns the length in bytes. – Rylee Jun 17 '22 at 00:50
23
SELECT column_name, 
       character_maximum_length 
FROM   information_schema.columns 
WHERE  table_schema = Database() 
       AND -- name of your database 
       table_name = 'turno' 
       AND -- name of your table 
       column_name = 'nombreTurno' -- name of the column

INFORMATION_SCHEMA.COLUMNS

If you wanna Whole Table size then use this

SELECT table_name                                                 AS "Tables", 
       Round(( ( data_length + index_length ) / 1024 / 1024 ), 2) "Size in MB" 
FROM   information_schema.tables 
WHERE  table_schema = "$db_name" 
ORDER  BY ( data_length + index_length ) DESC;

Edit

SELECT column_name, 
       character_maximum_length 
FROM   information_schema.columns 
WHERE  table_schema = 'websi_db1' 
       AND table_name = 'thread' 
       AND column_name = 'title'

Source

Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • Thank you for the response! I'm new to SQL so I'm not sure if I'm putting the database name in the right place. Does this look right? select COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_schema = DATABASE(websi_db1) AND table_name = 'thread' AND COLUMN_NAME = 'title' – user1446650 Feb 14 '14 at 06:46
  • I have updated with my answer. Can you check in my answer. Mark useful if it's helped you – Vignesh Kumar A Feb 14 '14 at 06:49
  • 3
    Thank you for the response. However it returned the max content length a column can have. I'm looking for the size of the entire column (all rows in specific column within table). – user1446650 Feb 14 '14 at 06:54
  • And that will show me the size of the column in kb? (the total size of all rows of data in that column) What would the entire query look like? Thank you – user1446650 Feb 14 '14 at 07:20
  • You may get table size in kb by abouve query not columns size – Vignesh Kumar A Feb 14 '14 at 07:23
6

If you are doing this from a script, you can run this after you have established a connection (and hence the database is already selected):

SELECT  character_maximum_length
FROM information_schema.columns
WHERE table_name = ? AND column_name = ?

Replace the ?'s with the name of your table and the name of your column, respectively.

insaner
  • 1,641
  • 16
  • 28