Slow MySql query (optimizing LEFT OUTER JOIN)

Question

I have Mysql query, something like this:

SELECT 
    Main.Code,
    Nt,
    Ss,
    Nac,
    Price,
    Ei,
    Quant,
    Dateadded,
    Sh,
    Crit,
    CAST(Ss * Quant AS DECIMAL (10 , 2 )) AS Qss,
    CAST(Price * Quant AS DECIMAL (10 , 2 )) AS Qprice,
    `Extra0`.`Value`
FROM
    Main
        LEFT OUTER JOIN
    `Extra_fields` AS `Extra0` ON `Extra0`.`Code` = `Main`.`Code`
        AND `Extra0`.`Nf` = 2
ORDER BY `Code`

The query is very slow (about 10 sec.). The query without this part: LEFT OUTER JOIN Extra_fields AS Extra0 ON Extra0.Code = Main.Code AND Extra0.Nf=2 is fast.

Is there some way to optimize first query?

Create required index on Code and Nf columns

Gurwinder Singh
– Gurwinder Singh

2017-01-28 07:16:39 +00:00
Commented Jan 28, 2017 at 7:16 — Gurwinder Singh
– Gurwinder Singh, Commented Jan 28, 2017 at 7:16
Please provide SHOW CREATE TABLE for both tables.

Rick James
– Rick James

2017-01-29 05:08:57 +00:00
Commented Jan 29, 2017 at 5:08 — Rick James
– Rick James, Commented Jan 29, 2017 at 5:08

Bill Karwin · Accepted Answer · 2017-01-28 07:27:53Z

1

You want to add an index on the joined table to help look up values by Code and Nf, then add the Value column so it can satisfy the column you need for the select-list:

ALTER TABLE Extra_fields ADD KEY (Code, Nf, Value);

You may benefit by adding an index on Main.Code so it reads the table in sorted order without having to do a filesort:

ALTER TABLE Main ADD KEY (Code);

I ran EXPLAIN on your query and got this:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Main
   partitions: NULL
         type: index
possible_keys: NULL
          key: Code
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Extra0
   partitions: NULL
         type: ref
possible_keys: code
          key: code
      key_len: 10
          ref: test.Main.Code,const
         rows: 1
     filtered: 100.00
        Extra: Using index

The first table has no filesort. I had to use ...FROM Main FORCE INDEX(Code)... but it could be because I tested with no rows in the table.

The second table shows it is using an index-only access method ("Extra: Using index"). I assume only three columns from Extra_fields are referenced, and all other columns are from Main.

answered Jan 28, 2017 at 7:27

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

Better yet, Extra_fields, since it smells like an EAV type schema, should have PRIMARY KEY(Code, Nf) and no AUTO_INCREMENT.

Collectives™ on Stack Overflow

Slow MySql query (optimizing LEFT OUTER JOIN)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related