Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

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

    
possible duplicate of How do I use EXPLAIN to predict performance of a MySQL query? –  Tichodroma Jul 27 '12 at 9:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.