Home » Performance » MySQL » help on optimizing INDEX
| help on optimizing INDEX [message #302] |
Sat, 28 October 2006 19:10  |
 Leeloo
Messages: 2
Registered: October 2006 |
Junior Member |
|
|
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
|
|
| |
| Re: help on optimizing INDEX [message #304 is a reply to message #303 ] |
Mon, 30 October 2006 04:28  |
Leeloo
Messages: 2
Registered: October 2006 |
Junior Member |
|
|
Thanks for your quit answer.
I need a few more explanations 
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.
|
|
| |
Goto Forum:
Current Time: Sun Jul 5 22:20:47 EDT 2009
Total time taken to generate the page: 0.01450 seconds |
Members
Search
Help
Register
Login
Home
