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
    
possible duplicate of How do I use EXPLAIN to predict performance of a MySQL query? –  Tichodroma Jul 27 '12 at 9:44
add comment

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

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

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.