0

I've got a "slow query" and didn't find the "right" index to avoid the slow query.

The query is :

SELECT c.uid FROM tx_gwcalendar_competition c,tx_gestionprofildb_discipline d WHERE c.hidden=0 and c.deleted=0 and c.discipline=d.uid and d.usergroup=19 LIMIT 1;

and my tables are:

CREATE TABLE IF NOT EXISTS `tx_gwcalendar_competition` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `tstamp` int(11) NOT NULL DEFAULT '0',
  `crdate` int(11) NOT NULL DEFAULT '0',
  `cruser_id` int(11) NOT NULL DEFAULT '0',
  `sys_language_uid` int(11) NOT NULL DEFAULT '0',
  `l10n_parent` int(11) NOT NULL DEFAULT '0',
  `l10n_diffsource` mediumtext,
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `title` tinytext,
  `discipline` int(11) NOT NULL DEFAULT '0',
  `dept` tinytext,
  `ville` tinytext,
  `distance` tinytext,
  `date` int(11) NOT NULL DEFAULT '0',
  `description` text,
  PRIMARY KEY (`uid`),
  KEY `parent` (`pid`),
  KEY `deleted` (`deleted`,`hidden`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8659 ;

and 

CREATE TABLE IF NOT EXISTS `tx_gestionprofildb_discipline` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `tstamp` int(11) NOT NULL DEFAULT '0',
  `crdate` int(11) NOT NULL DEFAULT '0',
  `cruser_id` int(11) NOT NULL DEFAULT '0',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `libelle` tinytext,
  `description` text,
  `usergroup` int(11) NOT NULL DEFAULT '0',
  `sportup_tag` tinytext,
  `form_mutation` text,
  `documents` text,
  `mutation` tinyint(3) NOT NULL DEFAULT '0',
  `nolicence` tinyint(3) NOT NULL DEFAULT '0',
  `agendahtml` text,
  `objectifhtml` text,
  `havestat` tinyint(3) NOT NULL DEFAULT '0',
  `description_conseil` text,
  `frais_admin` tinytext,
  PRIMARY KEY (`uid`),
  KEY `parent` (`pid`),
  KEY `deleted_hidden_libelle` (`deleted`,`hidden`,`libelle`(20))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=53 ;

When I Run a explain I got that :

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  c   ALL deleted NULL    NULL    NULL    8658    Using where
1   SIMPLE  d   eq_ref  PRIMARY PRIMARY 4   tbs888dbnew.c.discipline    1   Using where

I try to put an index on delete / hidden, but now change I still with 8658 key_len for the first row, like If I didn't put any index... my knowledge in mysql are limited, so I didn't know what to do (and if it's possible...).

So if someone have any advice, feel free.

Thanks you very much

Mitchum
  • 107
  • 2
  • 16

1 Answers1

0

Try to change the order of deleted and hidden in your deleted_hidden_libelle index definition, or change the order they appear in the where clause of your query.

They should appear in the index in the same order they appear in the where clause.

In addition, you should add an index for the field you are using to JOIN the tables :

KEY `discipline` (`discipline`)
Eran
  • 387,369
  • 54
  • 702
  • 768