Performance question

MySQL & MariaDB Other MySQL® Questions
1

Hi, I have a question about MySQL performance.

I have the following three tables

CREATE TABLE articulos (
Código varchar(10) collate latin1_spanish_ci NOT NULL,
Descripción varchar(50) collate latin1_spanish_ci NOT NULL,
Usa_Talles char(1) collate latin1_spanish_ci NOT NULL,
Usa_Colores char(1) collate latin1_spanish_ci NOT NULL,
PRIMARY KEY (Código),
KEY Descripción (Descripción)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

CREATE TABLE articulos_colores (
Código varchar(4) collate latin1_spanish_ci NOT NULL,
Descripción varchar(30) collate latin1_spanish_ci NOT NULL,
PRIMARY KEY (Código),
KEY Descripción (Descripción)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

CREATE TABLE articulos_talles (
Código tinyint(4) unsigned NOT NULL,
Descripción varchar(20) collate latin1_spanish_ci NOT NULL,
PRIMARY KEY (Código),
KEY Descripción (Descripción)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

Articulos has about 750 records, articulos_colores has about 500 and articulos_talles as 8.

I use the following query to return the full articles combinations about the articles with their respective colors and sizes (depending on Usa_Colores equals ‘S’ and the same for Usa_Talles (size in spanish))

SELECT articulos.Código as codart,
articulos_colores.Código as codcol,
articulos_talles.Código as codtalle
FROM articulos
LEFT JOIN articulos_colores ON articulos.Usa_Colores=‘S’ XOR articulos_colores.Código=‘0000’
LEFT JOIN articulos_talles ON articulos.Usa_Talles=‘S’ XOR articulos_talles.Código=0;

This query gives me the results (about 180.000 records) in 8 seconds. I think that is too much for the little count of records.

The “EXPLAIN” in the above query gives me this info
±—±------------±------------------±------±----------- —±--------±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------------------±------±----------- —±--------±--------±-----±-----±------------+
| 1 | SIMPLE | articulos | ALL | NULL | NULL | NULL | NULL | 250 | |
| 1 | SIMPLE | articulos_colores | index | NULL | PRIMARY | 6 | NULL | 761 | Using index |
| 1 | SIMPLE | articulos_talles | index | NULL | PRIMARY | 1 | NULL | 8 | Using index |
±—±------------±------------------±------±----------- —±--------±--------±-----±-----±------------+
3 rows in set

I’m relatively new to MySql… what should I do in order to improve performance?

(My installation is mysql 5.0.45 in Windows XP, P4 1.8ghz, 1.5gb ram)

Thanks in advance,
Mauro.

2

Your joins do look strange… what do you want to do?

A normal join should look like:

SELECT * FROM a
LEFT JOIN b ON a.id = b.a_id

For performance the tables should be connected by a simple condition.

And the fields used for the join should be indexed. This is valid for all SQL database engines.

3

Hi,

I discovered that the " articulos.Usa_Colores=‘S’ " and " articulos.Usa_Talles=‘S’ " are the conditions slowing the query. If I isolate them (first using only them and in another query using the other part of the full ON conditions) they also makes the query slow. I tried changing the field type to something but char or adding more indexes with no result.

I downloaded SQLServer express and made the same test for comparison and got basically the same benchmark results.

I solved the problem making a little redesign of my app in order to avoid usign this type of query. My surprise was that complex queries are faster than this one that is, at my opinion, nothing strange.

Best regards,
Mauro.

4

Get rid of the XOR stuff in the join syntax and move it to
the WHERE clause. You want a common key (Codigo?) to join
on between the parent/child tables.

You want something like:

SELECT
???
FROM articulos
LEFT JOIN articulos_colores ON articulos.Codigo = articulos_colores.Codigo
LEFT JOIN articulos_talles ON articulos.Codigo= articulos_talles.Código
WHERE ???

You also want this common key to be of the same data type
and size across the 3 tables. If Codigo can’t satisfy these
constraints, consider making up a auto_increment primary key
for all tables and joining on it.