Menu
I am just starting to investigate optimization for my MySQL database. From what I'm reading, indexing seems like a good idea - so I am wanting to create an index on one of my VARCHAR columns, on a table using the MyISAM engine.
From what I'm reading, I understand that an index is limited to a size of 1,000 bytes. A VARCHAR character is 3 bytes in size, though. Does that mean that if I want to index a VARCHAR column with 50 rows, I need an index prefix of 6 characters? (1,000 bytes / 50 rows / 3 bytes per character = 6.66)
If so, that seems a little comiplicated - which is why I'm questioning my understanding. It seems pretty odd that you would only be able to index 333 rows in a VARCHAR column, using a prefix of 1 character.
Am I missing something?
761
Query to check index size in Oracleselect sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='&INDEX_NAME'; select sum(bytes)/1024/1024 as "Index Size (MB)" from user_segments where segment_name='&INDEX_NAME';
In general, MySQL only allows you to create up to 16 indexes on a given table. If you are using a PRIMARY KEY index, you can only have one primary key per table. FULLTEXT, UNIQUE INDEXes, and INDEXes do not have this limitation.
MySQL has three types of indexes: INDEX, UNIQUE (which requires each row to have a unique value), and PRIMARY KEY (which is just a particular UNIQUE index). Table 3.5 lists the indexes I propose for the accounting database.
A table can contain a maximum of 64 secondary indexes. If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.
From what I've read, I understand that an index is limited to a size of 1000 bytes.
Index key length is limited to 1000 bytes in MyISAM, 767 bytes in InnoDB (per column).
This means that you cannot index a single UTF8 column more than 333 characters long in a MyISAM table (when calculating the max index size, MySQL assumes 3 bytes per character, though actual length may be much smaller)
You can create as many indexed records as you need.
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
