1

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly.

Suppose having the following MySQL table:

CREATE TABLE IF NOT EXISTS `spec_99_4_1_attivita` (
    `id_attivita` int(10) unsigned NOT NULL DEFAULT '0',
    `id_gruppo` int(10) unsigned NOT NULL DEFAULT '0',
    `id_valutazione` int(10) unsigned NOT NULL DEFAULT '0',
    `id_azienda` int(10) unsigned NOT NULL DEFAULT '1',
    `id_sede` int(10) unsigned NOT NULL DEFAULT '1',
    `revisione_documento` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_attivita`,`id_gruppo`,`id_valutazione`,`id_azienda`,`id_sede`,`revisione_documento`),
    CONSTRAINT `fk_id_attivita_spec_99_4_1_attivita` FOREIGN KEY (`id_attivita`, `id_azienda`, `id_sede`, `revisione_documento`) REFERENCES `gen_12_1_aree_lavoro` (`id_area_lavoro`, `id_azienda`, `id_sede`, `revisione_documento`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_id_gruppo_spec_99_4_1_attivita` FOREIGN KEY (`id_gruppo`, `id_valutazione`, `id_azienda`, `id_sede`, `revisione_documento`) REFERENCES `spec_99_4_1_gruppi` (`id_gruppo`, `id_valutazione`, `id_azienda`, `id_sede`, `revisione_documento`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

would a query like:

SELECT * FROM spec_99_4_1_attivita WHERE id_azienda = 1 AND id_sede = 2 AND revisione_documento = 3 AND id_valutazione = 4 AND id_gruppo = 5

use the indexes, or would it require a full table scan?

And, more in general, how would you determine if a query is going to use indexes or not? Could you make some general examples for both cases?

migrated from stackoverflow.com Aug 24 '12 at 8:27

This question came from our site for professional and enthusiast programmers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.