[mysql] unico indice su due colonne o due indici distinti

[debug]

Per mettere in join diverse tabelle (non molte) uso tabelle aggiuntive che contengono l'id di una e l'id dell'altra.
Ho fatto l'explain di una select con le due soluzioni, ma ho dei dubbi sulle differenze di performance (in rosso le differenze):

- unico indice su due colonne

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	authors	system	PRIMARY	NULL	NULL	NULL	1
1	SIMPLE	slugs	const	slug,post_id	slug	767	const	1
1	SIMPLE	join_posts_vs_authors	const	idx	idx	8	const,const	1	Using index
1	SIMPLE	posts	const	PRIMARY	PRIMARY	4	const	1
1	SIMPLE	join_posts_vs_post_types	ref	idx	idx	4	const	1	Using index
1	SIMPLE	post_types	eq_ref	PRIMARY	PRIMARY	4	test.join_posts_vs_post_types.post_type_id	1

- due indici distinti, uno per ogni colonna id

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	authors	system	PRIMARY	NULL	NULL	NULL	1
1	SIMPLE	slugs	const	slug,post_id	slug	767	const	1
1	SIMPLE	posts	const	PRIMARY	PRIMARY	4	const	1
1	SIMPLE	join_posts_vs_authors	ref	author_id,post_id	post_id	4	const	1	Using where
1	SIMPLE	join_posts_vs_post_types	ref	post_id,post_type_id	post_id	4	const	1
1	SIMPLE	post_types	eq_ref	PRIMARY	PRIMARY	4	test.join_posts_vs_post_types.post_type_id	1

Grazie

Ciao!

[debug]

Ah mi son dimenticato di scrivere che la query è questa:

SELECT
  posts.id Post_Id, posts.title Post_Title, posts.content Post_Content, posts.datetime Post_Datetime, posts.status Post_Status,
  authors.name Author_Name,
  post_types.name Post_Type
FROM posts
INNER JOIN slugs 
  ON slugs.post_id = posts.id
INNER JOIN ( join_posts_vs_authors, authors )
  ON join_posts_vs_authors.post_id = posts.id AND authors.id = join_posts_vs_authors.author_id
INNER JOIN ( join_posts_vs_post_types, post_types )
  ON join_posts_vs_post_types.post_id = posts.id AND post_types.id = join_posts_vs_post_types.post_type_id
WHERE slugs.slug =  'xxx-yyy-zz' 

Ciao!

[debug]

nada?


Ciao!

[razzoli]

Sinceramente non vedo grandi differenze, sono tutte comparazioni const e usi sempre un indice. Hai problemi di prestazioni?

Lo slug è una sorta di id testuale del post? Hai pensato di registrare l'md5 dello slug in un campo indicizzato? La chiave sarebbe lunga 32 invece di 767...

[debug]

Sinceramente non vedo grandi differenze, sono tutte comparazioni const e usi sempre un indice. Hai problemi di prestazioni?

Sto studiando una struttura del db che possa reggere alto traffico (più in lettura che in scrittura), quindi per ora non ho problemi, ma voglio evitarli quando il progetto sara in produzione (milioni di record con migliaia di interrogazioni).
Comunque di differenze ce ne sono, anche se, credo, minime, almeno dal report che ho postato.
Per esempio nell'Extra del primo report leggo 2 using index, mentre nel secondo report c'è un solo using where (che credo sia peggiore dell'altro).

Lo slug è una sorta di id testuale del post? Hai pensato di registrare l'md5 dello slug in un campo indicizzato? La chiave sarebbe lunga 32 invece di 767...

A questo non avevo pensato (key_len), grazie.


Ciao!!!

[razzoli]

Per esempio nell'Extra del primo report leggo 2 using index, mentre nel secondo report c'è un solo using where (che credo sia peggiore dell'altro).

Entrambe le query usano un indice. La differenza è che "Using index" significa che usa solo l'indice, senza dover accedere ai dati. Si parla di Covering Index. Però... stiamo parlando di una sola riga, quindi anche se deve accedere ai dati non cambia nulla

Visto che ci sono così poche differenze (più che altro, non credo incidano sulle prestazioni) io userei l'indice su una colonna.

[debug]

Entrambe le query usano un indice. La differenza è che "Using index" significa che usa solo l'indice, senza dover accedere ai dati. Si parla di Covering Index. Però... stiamo parlando di una sola riga, quindi anche se deve accedere ai dati non cambia nulla

E se parlassimo di estrarre X campi? Diciamo per esempio un centinaio.

Visto che ci sono così poche differenze (più che altro, non credo incidano sulle prestazioni) io userei l'indice su una colonna.

Come mai questa scelta?


Ciao!

[razzoli]

E se parlassimo di estrarre X campi? Diciamo per esempio un centinaio.

Non ho capito la domanda. MySQL usa i covering index solo se tutti i campi che deve leggere sono indicizzati, altrimenti dovrà leggere anche i dati. Se fai SELECT id WHERE nome='x' e nome è indicizzato, l'indice è "covering" per questa query. Se selezioni anche un campo che non è indicizzato, l'indice non è covering.

Mi stai chiedendo se conviene fare un indice di 100 campi? No, occuperebbe tanta memoria e le operazioni di lettura dell'indice sarebbero più lente.

I covering index fanno la differenza se devi leggere molte righe.

Come mai questa scelta?

In generale l'indice deve essere as small as possible. E' giusto aggiungere una colonna solo se ci sono dei vantaggi. Ma in questo caso, tutti e due i query plan sono praticamente perfetti. (a parte la lunghezza dell'indice slug)

Ciao!

Ciao

[debug]

Non ho capito la domanda. MySQL usa i covering index solo se tutti i campi che deve leggere sono indicizzati, altrimenti dovrà leggere anche i dati. Se fai SELECT id WHERE nome='x' e nome è indicizzato, l'indice è "covering" per questa query. Se selezioni anche un campo che non è indicizzato, l'indice non è covering.

Mi stai chiedendo se conviene fare un indice di 100 campi? No, occuperebbe tanta memoria e le operazioni di lettura dell'indice sarebbero più lente.

No intendevo se dovessi estrarre un numero X di righe in base ad una select del tipo Where author_id = 'xy' (che corrisponde a tutti i post di quell'autore), oppure tutti i post in un range temporale.


Ciao!!

[razzoli]

No intendevo se dovessi estrarre un numero X di righe in base ad una select del tipo Where author_id = 'xy' (che corrisponde a tutti i post di quell'autore), oppure tutti i post in un range temporale.

Sì in quel caso un covering index è più efficiente... ma se la query è efficiente lo stesso, non aggiungere campi.