Not the answer you need?
Register and ask your own question!

help on optimizing INDEX

LeelooLeeloo EntrantCurrent User Role Beginner
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

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    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.
  • LeelooLeeloo Entrant Current User Role Beginner
    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 :
    • tutorat_choix_idutilisateur
    • tutorat_choix_idmodule tutorat_choix_idmodule

    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.