help on optimizing INDEX

MySQL & MariaDB Other MySQL® Questions
1

Hello,

i need some advices on my actual INDEX

============================================================ ==
Schema
============================================================ ==

CREATE TABLE choix (
idchoix int(11) NOT NULL auto_increment,
idmodule int(11) NOT NULL default ‘0’,
evaldebut varchar(10) NOT NULL default ‘-1’,
evalfin varchar(10) NOT NULL default ‘-1’,
idutilisateur int(11) NOT NULL default ‘0’,
idprofile int(11) default NULL,
permanent int(1) NOT NULL default ‘0’,
idsousthemechoix int(1) default NULL,
etat_lecon varchar(200) default ‘0’,
masquer tinyint(1) NOT NULL default ‘0’,
PRIMARY KEY (idchoix),
KEY tutorat_choix_idutilisateur (idutilisateur),
KEY tutorat_choix_idmodule (idmodule),
KEY tutorat_choix_idmodule_idutilisateur (idmodule,idutilisateur),
KEY tutorat_choix_idutilisateur_idmodule (idutilisateur,idmodule),
KEY tutorat_choix_idsousthemechoix (idsousthemechoix),
KEY etat_lecon_2 (etat_lecon),
KEY etat_lecon_3 (etat_lecon)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

============================================================ ==
Information on my databases
============================================================ ==

MyISAM file: choix.MYI
Record format: Packed
Character set: latin1_swedish_ci ( 8 )
File-version: 1
Creation time: 2006-10-17 16:28:13
Recover time: 2006-10-27 22:50:23
Status: open,changed
Auto increment key: 1 Last value: 13916712
Data records: 11107899 Deleted blocks: 0
Datafile parts: 11124831 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 399944304 Keyfile length: 1043645440
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 248

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 4 unique long 1 1024 1024
2 30 4 multip. long 305 99142656 1024
3 6 4 multip. long 15049 208405504 1024
4 6 4 multip. long 15049 318073856 1024
30 4 long 1
5 30 4 multip. long 305 483473408 1024
6 4 long 1
6 42 4 multip. long NULL 807 645105664 1024
7 46 200 multip. char packed stripped NULL 5545475 848878592 1024
8 46 200 multip. char packed stripped NULL 5545475 945477632 1024

Field Start Length Nullpos Nullbit Type
1 1 1
2 2 4 no zeros
3 6 4 no zeros
4 10 10 no endspace
5 20 10 no endspace
6 30 4 no zeros
7 34 4 1 1 no zeros
8 38 4 no zeros
9 42 4 1 2 no zeros
10 46 200 1 4 no endspace
11 246 1 no zeros

============================================================ ==

An for a simple query, it took ~20 sec

mysql> select count(distinct idutilisateur) from choix order by idutilisateur;
±------------------------------+
| count(distinct idutilisateur) |
±------------------------------+
| 36355 |
±------------------------------+
1 row in set (18.01 sec)

mysql> explain select count(distinct idutilisateur) from choix;
±—±------------±------±------±--------------±------- ---------------------±--------±-----±---------±--------- —+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±------- ---------------------±--------±-----±---------±--------- —+
| 1 | SIMPLE | choix | index | NULL | tutorat_choix_idutilisateur | 4 | NULL | 11107899 | Using index |
±—±------------±------±------±--------------±------- ---------------------±--------±-----±---------±--------- —+
1 row in set (0.00 sec)

I have to make some change on the indeces ?

Thanks

2

Hi,

First check this out

http://www.mysqlperformanceblog.com/2006/08/17/duplicate-ind exes-and-redundant-indexes/

You have both types of bad indexes in your schema.

Second - which MySQL version are you using ? MySQL 5.0 could in theory use loose index scan to find distinct values from the index, while I’m not 100% sure.

You have index scan in explain - so you have right index but it is still way too slow to scan it.

3

Thanks for your quit answer.

I need a few more explanations confused:

The version I run : 4.1.5-gamma-log

I use a script in order to find possible duplicate indeces

http://www.xaprb.com/blog/2006/08/28/how-to-find-duplicate-a nd-redundant-indexes-in-mysql/

So for this case :

CREATE TABLE choix (
idchoix int(11) NOT NULL auto_increment,
idmodule int(11) NOT NULL default ‘0’,
evaldebut varchar(10) NOT NULL default ‘-1’,
evalfin varchar(10) NOT NULL default ‘-1’,
idutilisateur int(11) NOT NULL default ‘0’,
idprofile int(11) default NULL,
permanent int(1) NOT NULL default ‘0’,
idsousthemechoix int(11) default NULL,
etat_lecon varchar(200) default ‘0’,
masquer tinyint(1) NOT NULL default ‘0’,
PRIMARY KEY (idchoix),
KEY tutorat_choix_idutilisateur (idutilisateur),
KEY tutorat_choix_idmodule (idmodule),
KEY tutorat_choix_idmodule_idutilisateur (idmodule,idutilisateur),
KEY tutorat_choix_idutilisateur_idmodule (idutilisateur,idmodule),
KEY etat_lecon_2 (etat_lecon),
KEY etat_lecon_3 (etat_lecon)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The script give me

Table tutorat.choix has possible duplicate indexes:
BTREE idmodule,idutilisateur
BTREE idmodule
BTREE idutilisateur,idmodule
BTREE idutilisateur
BTREE etat_lecon
BTREE etat_lecon

So i need to remove the index :

[LIST]
[] tutorat_choix_idutilisateur
[] tutorat_choix_idmodule tutorat_choix_idmodule
[/LIST]

Is this right ?
Would be ok for SELECT, INSERT, DELETE, UPDATE query?

For another of my table, I’v got :

CREATE TABLE service (
idservice int(11) NOT NULL auto_increment,
libservice varchar(200) default NULL,
idutilisateur int(11) default NULL,
idgroupe int(11) default NULL,
PRIMARY KEY (idservice),
UNIQUE KEY id_service (idservice)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Table tutorat;.service has possible duplicate indexes:
BTREE idservice
BTREE idservice

No need to have an UNIQUE index on the primary KEY, it will cost the same amount of time to scan or acces the index ?

Thanks in advance for the time passed answering my questions.