Mysql too slow on simple query between two tables

Question

Good morning, I've two tables, ANALISI with 1462632 records and PAZIENTE with 1408146 records, this simple count using one of the index of PAZIENTE require about 30 seconds to give back about 65000 records

SELECT COUNT(analisi0_.ID_ANALISI) AS col_0_0_
FROM Analisi analisi0_
  INNER JOIN Paziente paziente1_ ON analisi0_.ID_PAZIENTE = paziente1_.ID_PAZIENTE
WHERE (paziente1_.nome LIKE 'MARIA%')

I've also tried adding an index on analisi0_.ID_PAZIENTE but with no good results. Is there a way to enhance performance?

This is the corrisponding explain that seems ok to me

CREATE TABLE ANALISI
(
   ID_ANALISI               INT             UNSIGNED NOT NULL AUTO_INCREMENT,
   ID_PAZIENTE              INT             UNSIGNED NOT NULL,
   ID_SESSIONE              INT             UNSIGNED NOT NULL,
   TRACCIATO                TINYINT         UNSIGNED NOT NULL,
   CAMPIONE                 VARCHAR(30),
   ID_PATOLOGICO            TINYINT         UNSIGNED,
   REPARTO                  VARCHAR(40),
   TOTALE_PROTEINE          FLOAT,   
   RAPP_AG                  FLOAT,
   ID_ANALISI_LINK          INT             UNSIGNED,
   ID_ANALISI_IFE           INT             UNSIGNED,   
   ID_ANALISI_DATI          INT             UNSIGNED,  
   ID_ANALISI_NOTA          INT             UNSIGNED,
   DATA_MODIFICA            DATETIME,      
   ID_UTENTE_MODIFICA       SMALLINT        UNSIGNED,
   DATA_VALIDAZIONE         DATETIME,
   ID_TIPO_VALIDAZIONE      TINYINT         UNSIGNED NOT NULL,
   ID_UTENTE_VALIDAZIONE    SMALLINT        UNSIGNED,   
   DATA_CANCELLAZIONE       DATETIME,
   ID_UTENTE_CANCELLAZIONE  SMALLINT        UNSIGNED,      
   PRIMARY KEY (ID_ANALISI), 
   INDEX IDX_CAMPIONE (CAMPIONE),
   INDEX IDX_REPARTO (REPARTO),   
   CONSTRAINT FK_ANALISI_PAZIENTE             FOREIGN KEY (ID_PAZIENTE)             REFERENCES PAZIENTE(ID_PAZIENTE),
   CONSTRAINT FK_ANALISI_SESSIONE             FOREIGN KEY (ID_SESSIONE)             REFERENCES SESSIONE(ID_SESSIONE),
   CONSTRAINT FK_ANALISI_PATOLOGICO           FOREIGN KEY (ID_PATOLOGICO)           REFERENCES PATOLOGICO(ID_PATOLOGICO),
   CONSTRAINT FK_ANALISI_TIPO_VALIDAZIONE     FOREIGN KEY (ID_TIPO_VALIDAZIONE)     REFERENCES TIPO_VALIDAZIONE(ID_TIPO_VALIDAZIONE),
   CONSTRAINT FK_ANALISI_UTENTE_MODIFICA      FOREIGN KEY (ID_UTENTE_MODIFICA)      REFERENCES UTENTE(ID_UTENTE), 
   CONSTRAINT FK_ANALISI_UTENTE_VALIDAZIONE   FOREIGN KEY (ID_UTENTE_VALIDAZIONE)   REFERENCES UTENTE(ID_UTENTE), 
   CONSTRAINT FK_ANALISI_UTENTE_CANCELLAZIONE FOREIGN KEY (ID_UTENTE_CANCELLAZIONE) REFERENCES UTENTE(ID_UTENTE),    
   CONSTRAINT FK_ANALISI_ANALISI_LINK         FOREIGN KEY (ID_ANALISI_LINK)         REFERENCES ANALISI(ID_ANALISI),
   CONSTRAINT FK_ANALISI_ANALISI_IFE          FOREIGN KEY (ID_ANALISI_IFE)          REFERENCES ANALISI_IFE(ID_ANALISI_IFE),
   CONSTRAINT FK_ANALISI_ANALISI_NOTA         FOREIGN KEY (ID_ANALISI_NOTA)         REFERENCES ANALISI_NOTA(ID_ANALISI_NOTA),
   CONSTRAINT FK_ANALISI_ANALISI_DATI         FOREIGN KEY (ID_ANALISI_DATI)         REFERENCES ANALISI_DATI(ID_ANALISI_DATI)
)
ENGINE=InnoDB;

---

CREATE TABLE PAZIENTE
(
   ID_PAZIENTE          INT           UNSIGNED NOT NULL AUTO_INCREMENT,
   ID_PAZIENTE_LAB      VARCHAR(20),
   COGNOME              VARCHAR(30),
   NOME                 VARCHAR(30),
   DATA_NASCITA         DATE,
   ID_SESSO             TINYINT UNSIGNED NOT NULL,
   RECAPITO             VARCHAR(50),
   CODICE_FISCALE       VARCHAR(30),
   ID_SPECIE            TINYINT UNSIGNED NOT NULL,
   PRIMARY KEY (ID_PAZIENTE), 
   INDEX IDX_DATA_NASCITA (DATA_NASCITA),
   INDEX IDX_COGNOME (COGNOME),
   INDEX IDX_NOME (NOME),
   INDEX IDX_SESSO (ID_SESSO),
   CONSTRAINT FK_PAZIENTE_SPECIE FOREIGN KEY (ID_SPECIE) REFERENCES SPECIE(ID_SPECIE),  
   CONSTRAINT FK_PAZIENTE_SESSO FOREIGN KEY (ID_SESSO) REFERENCES SESSO(ID_SESSO)   
)
ENGINE=InnoDB;

Answer 1

In InnoDB every index contains the primary key implicitly.

The explain plan shows that index IDX_NOME is used on table Paziente. The DBMS looks up the name in the index and finds ID_PAZIENTE in there, which is the key we need to access the other table. So there is nothing to add. (In another DBMS we would have added a composite index on (NOME, ID_PAZIENTE) for this to happen.)

Then there is table Analisi to consider. We find a record via FK_ANALISI_PAZIENTE which contains the ID_PAZIENTE which is used to find the match, and implicitly the primary key ID_ANALISI which could be used to access the table, but this is not even necessary, beacuse we have all information we need from the index. There is nothing left that we need to find in the table. (Again, in another DBMS we would have added a composite index on (ID_PAZIENTE, ID_ANALISI) to have a covering index.)

So what happens is merely: read one index in order to read the other index in order to count. Perfect. There is nothing to add.

We could replace COUNT(analisi0_.ID_ANALISI) with COUNT(*) as the former only says "count records where ID_ANALISI is not null", which is always the case as ID_ANALISI is the table's primary key. So it's simpler to use the latter and say "count records". However, I don't expect this to speed up the query significantly if at all.

So from a query point of view, there is nothing to speed this up. Here are further things that come to mind:

• Partitioned tables? No, I would see no benefit in this. It could be faster were the query executed in parallel threads then, but as far as I know, there is no parallel execution on multiple partitions in MySQL. (I may be wrong though.)
• Defragmenting the tables? No, the tables themselves are not even accessed in the query.
• That leaves us with: Buy better hardware. (Sorry not to have any better advice for you.)