•  

Announcement

Announcement Module
Collapse
No announcement yet.

Performance question

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance question

    #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.
    Tags: None
  • #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.

    Comment

    • #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.

      Comment

      • #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.

        Comment

        Working...
        X