MySQL indexes: how do they work?

Question

I am a complete newbie with MySQL indexes. I have several MyISAM tables in MySQL 5.0x that have utf8 encodings and map to 100k + records. Primary keys are usually integer. Many columns in each table can have duplicate values.

I need to quickly calculate, summarize, average, or otherwise perform custom calculations on any number of fields in each table, or join any number of others.

I found this page giving an overview of using the MySQL index: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html , but I'm still not sure that I use indexes correctly. Just when I think I made a perfect index from a collection of fields that I want to calculate, I get the error "index must be less than 1000 bytes."

Can someone explain how to most efficiently create and use indexes to speed up queries?

Caution: Mysql upgrade is not possible in this case. Using Navicat Light to administer db, but this application is not required.

Answer 1

MySQL, , B- ( , ), .

, , , :

CREATE TABLE mytable (
 id int unsigned auto_increment,
 column_a char(32) not null default '',
 column_b int unsigned not null default 0,
 column_c varchar(512),
 column_d varchar(512),
 PRIMARY KEY (id)
) ENGINE=MyISAM;

:

INSERT INTO mytable VALUES (1, 'hello', 2, null, null);
INSERT INTO mytable VALUES (2, 'hello', 3, 'hi', 'there');
INSERT INTO mytable VALUES (3, 'how', 4, 'are', 'you?');
INSERT INTO mytable VALUES (4, 'foo', 5, '', 'bar');

, column_a column_b, :

ALTER TABLE mytable ADD KEY (column_a, column_b);

B-, , :

hello-2
hello-3
how-4
foo-5

, column_a column_a AND column_b, , , . , :

SELECT ... FROM mytable WHERE column_a = 'hello';

, column_b, , , "". , :

SELECT ... FROM mytable WHERE column_b = '2';

, , , "2", .

. , , , PK, :

ALTER TABLE mytable ADD KEY (column_a, column_b, column_c, column_d);

:

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

32, 10, 512 512, 1066, 1000. , DID; :

hello-2-
hello-3-hi-there
how-4-are-you?
foo-5--bar

, column_c column_d, - 512 . 1000 , MySQL. , , , "" .

, InnoDB, MyISAM , (3500 ) - , ENGINE=InnoDB ENGINE=MyISAM . , , , , - .

, , . , / , . , , , . , , , , , , , .

Answer 2

, . , , EXPLAIN, , .

, , , , , , .

, , . , , .

, , , .

Answer 3

, . - , , , .

, , "". , , .

, /, , , 50% . , . , , .

Answer 4

, .

, , , 1000 MyISAM. http://dev.mysql.com/doc/refman/5.0/en/create-index.html:

, 10 :

CREATE INDEX part_of_name ON ( (10)); 10 , , , . , , INSERT.

Support for prefix and prefix lengths (where supported) - dependent storage mechanism. For example, a prefix can be up to 1000 bytes long for MyISAM tables and 767 bytes for InnoDB tables.

Perhaps you can try the FULLTEXT index for problematic columns.