MySQL View 20x slower than Select

Question

I have a query that selects ~8000 rows. When I execute the query it takes 0.1 sec.

When I copy the query into a view and execute the view it takes about 2 seconds. In the first row of explain it selects ~570K rows, i dont know why.

I dont understand the first Row and why it shows up only in the view explain

1 PRIMARY ALL NULL NULL NULL NULL

This is the query (yes i know im not a mysql pro and the query is not that efficent, but it works ans 0.1 sek would be ok for me. Does anyone know why it is so slow in a view?

MariaDB 10.5.9

select
   `xxxxxxx`.`auftraege`.`Zustandigkeit` AS `Zustandigkeit`,
   `xxxxxxx`.`auftraege`.`cms` AS `cms`,
   `xxxxxxx`.`auftraege`.`auftrag_id` AS `auftrag_id`,
   `xxxxxxx`.`angebot`.`angebot_id` AS `angebot_id`,
   `xxxxxxx`.`kunden`.`kunde_id` AS `kid`,
   `xxxxxxx`.`angebot`.`kunde_id` AS `kunde_id`,
   `xxxxxxx`.`kunden`.`firma` AS `firma`,
   `xxxxxxx`.`auftraege`.`gekuendigt` AS `gekuendigt`,
   `xxxxxxx`.`kunden`.`ansprechpartnerVorname` AS `ansprechpartnerVorname`,
   `xxxxxxx`.`kunden`.`ansprechpartner` AS `ansprechpartner`,
   `xxxxxxx`.`auftraege`.`ampstatus` AS `ampstatus`,
   `xxxxxxx`.`auftraege`.`autoMahnungen` AS `autoMahnungen`,
   `xxxxxxx`.`kunden`.`mail` AS `mail`,
   `xxxxxxx`.`kunden`.`ansprechpartnerAnrede` AS `ansprechpartnerAnrede`,
   case
      `xxxxxxx`.`kunden`.`ansprechpartnerAnrede` 
      when
         'm' 
      then
         concat('Herr ', ifnull(`xxxxxxx`.`kunden`.`ansprechpartnerVorname`, ''), ifnull(`xxxxxxx`.`kunden`.`ansprechpartner`, '')) 
      else
         concat('Frau ', ifnull(`xxxxxxx`.`kunden`.`ansprechpartnerVorname`, ''), ifnull(`xxxxxxx`.`kunden`.`ansprechpartner`, '')) 
   end
   AS `ansprechpartnerfullName`, `xxxxxxx`.`kunden`.`website` AS `website`, `xxxxxxx`.`personal`.`name_betrieb` AS `name_betrieb`, `xxxxxxx`.`kunden`.`prioritaet` AS `prioritaet`, `xxxxxxx`.`auftraege`.`infoemail` AS `infoemail`, `xxxxxxx`.`auftraege`.`keywords` AS `keywords`, `xxxxxxx`.`auftraege`.`ftp_h` AS `ftp_h`, `xxxxxxx`.`auftraege`.`ftp_u` AS `ftp_u`, `xxxxxxx`.`auftraege`.`ftp_pw` AS `ftp_pw`, `xxxxxxx`.`auftraege`.`lgi_h` AS `lgi_h`, `xxxxxxx`.`auftraege`.`lgi_u` AS `lgi_u`, `xxxxxxx`.`auftraege`.`lgi_pw` AS `lgi_pw`, `xxxxxxx`.`auftraege`.`autoRemind` AS `autoRemind`, `xxxxxxx`.`kunden`.`telefon` AS `telefon`, `xxxxxxx`.`kunden`.`mobilfunk` AS `mobilfunk`, `xxxxxxx`.`auftraege`.`kommentar` AS `kommentar`, `xxxxxxx`.`auftraege`.`phase` AS `phase`, `xxxxxxx`.`auftraege`.`datum` AS `datum`, `xxxxxxx`.`angebot`.`typ` AS `typ`, 
   case
      `xxxxxxx`.`auftraege`.`gekuendigt` 
      when
         '1' 
      then
         'Ja' 
      else
         'Nein' 
   end
   AS `Gekuendigt ? `, 
   (
      select
         count(`xxxxxxx`.`status`.`aenderung`) 
      from
         `xxxxxxx`.`status` 
      where
         `xxxxxxx`.`status`.`auftrag_id` = `xxxxxxx`.`auftraege`.`auftrag_id`
   )
   AS `aenderungen`,
   `xxxxxxx`.`auftraege`.`vertragStart` AS `vertragStart`,
   `xxxxxxx`.`auftraege`.`vertragEnde` AS `vertragEnde`,
   case
      `xxxxxxx`.`auftraege`.`zahlungsart` 
      when
         'U' 
      then
         'Überweisung' 
      when
         'L' 
      then
         'Lastschrift' 
      else
         'Unbekannt' 
   end
   AS `Zahlungsart`, `xxxxxxx`.`kunden`.`yyyyy_piwik` AS `yyyyy_piwik`, 
   (
      select
         max(`xxxxxxx`.`status`.`datum`) AS `mxDTst` 
      from
         `xxxxxxx`.`status` 
      where
         `xxxxxxx`.`status`.`auftrag_id` = `xxxxxxx`.`auftraege`.`auftrag_id` 
         and `xxxxxxx`.`status`.`typ` = 'SEO'
   )
   AS `mxDTst`,
   (
      select
         case
            `xxxxxxx`.`rechnungen`.`beglichen` 
            when
               'YES' 
            then
               'isOk' 
            else
               'isAffe' 
         end
         AS `neuUwe` 
      from
         (
            `xxxxxxx`.`zahlungsplanneu` 
            join
               `xxxxxxx`.`rechnungen` 
               on(`xxxxxxx`.`zahlungsplanneu`.`rechnungsnummer` = `xxxxxxx`.`rechnungen`.`rechnungsnummer`)
         )
      where
         `xxxxxxx`.`zahlungsplanneu`.`auftrag_id` = `xxxxxxx`.`auftraege`.`auftrag_id` 
         and `xxxxxxx`.`rechnungen`.`beglichen` <> 'STO' limit 1
   )
   AS `neuer`, 
   (
      select
         group_concat(`xxxxxxx`.`kunden_keywords`.`keyword` separator ',') 
      from
         `xxxxxxx`.`kunden_keywords` 
      where
         `xxxxxxx`.`kunden_keywords`.`kunde_id` = `xxxxxxx`.`kunden`.`kunde_id`
   )
   AS `keyword`,
   (
      select
         case
            count(0) 
            when
               0 
            then
               'Cool' 
            else
               'Uncool' 
         end
         AS `AusfallVor` 
      from
         `xxxxxxx`.`rechnungen` 
      where
         `xxxxxxx`.`rechnungen`.`rechnung_tag` < current_timestamp() - interval 15 day 
         and `xxxxxxx`.`rechnungen`.`kunde_id` = `xxxxxxx`.`kunden`.`kunde_id` 
         and `xxxxxxx`.`rechnungen`.`beglichen` = 'NO' limit 1
   )
   AS `Liquidiert` 
from
   (
((((`xxxxxxx`.`auftraege` 
      join
         `xxxxxxx`.`angebot` 
         on(`xxxxxxx`.`auftraege`.`angebot_id` = `xxxxxxx`.`angebot`.`angebot_id`)) 
      join
         `xxxxxxx`.`kunden` 
         on(`xxxxxxx`.`angebot`.`kunde_id` = `xxxxxxx`.`kunden`.`kunde_id`)) 
      left join
         `xxxxxxx`.`kunden_keywords` 
         on(`xxxxxxx`.`angebot`.`kunde_id` = `xxxxxxx`.`kunden_keywords`.`kunde_id`)) 
      join
         `xxxxxxx`.`personal` 
         on(`xxxxxxx`.`kunden`.`bearbeiter` = `xxxxxxx`.`personal`.`personal_id`)) 
      left join
         `xxxxxxx`.`status` 
         on(`xxxxxxx`.`auftraege`.`auftrag_id` = `xxxxxxx`.`status`.`auftrag_id`)
   )
group by
   `xxxxxxx`.`auftraege`.`auftrag_id` 
order by
   NULL

UPDATE 1

1. The View Itself (Duration 1.83 sec)

1.1 Create the View: This is the View i created, it only contains the query from above.

1.2 Executing the View: It takes 1.83 sek to execute the view

1.3 Analyze the View: This is the explain of the view

2. The view with added where clause (Duration 1.86 sec)

2.1 Analyze the View with added where clause @rick wanted me to add a where clause to the view, if i understood him correctly. This is the explain of the view, where i added a where clause, takes 1.86 sec.

3. The Query, that is the source of the view (Duration: 0.1 sec)

3.1 Execute the query directly This is the query, that is the source of the view, when i execute it directly to the server. It takes ~0.1 - 0.2 seconds.

3.2 Analyze the direct queryAnd this is the explain of the pure query.

Why the view is so much slower, by only cupsuling the query inside of the view?

Update 2

These are the indexes I have set ALTER TABLE angebot ADD INDEX angebot_idx_angebot_id (angebot_id);

ALTER TABLE auftraege ADD INDEX auftraege_idx_auftrag_id (auftrag_id);

ALTER TABLE kunden ADD INDEX kunden_idx_kunde_id (kunde_id);

ALTER TABLE kunden_keywords ADD INDEX kunden_keywords_idx_kunde_id (kunde_id);

ALTER TABLE personal ADD INDEX personal_idx_personal_id (personal_id);

ALTER TABLE rechnungen ADD INDEX rechnungen_idx_rechnungsnummer_beglichen (rechnungsnummer,beglichen);

ALTER TABLE rechnungen ADD INDEX rechnungen_idx_beglichen_kunde_id_rechnung (beglichen,kunde_id,rechnung_tag);

ALTER TABLE status ADD INDEX status_idx_auftrag_id (auftrag_id);

ALTER TABLE status ADD INDEX status_idx_typ_auftrag_id_datum (typ,auftrag_id,datum);

ALTER TABLE zahlungsplanneu ADD INDEX zahlungsplanneu_idx_auftrag_id (auftrag_id);

https://stackoverflow.com/questions/62832483/mysql-view-is-very-slow-why some users had the same problem like me, is there any way to disable the temptable algotithm that the view only executes the query without any changes, so the execution time is exactly the same as the direct query? — user3236231, Aug 05 '21 at 09:13

Rick James · Answer 1 · 2021-08-03T17:46:24.417

1

Be consistent between tables. kunde_id, for example, seems to be declared differently between tables. This may be preventing some obvious optimizations. (There are 6 JOINs that say func in EXPLAIN`.)

Remove the extra parentheses in JOINs. They may be preventing what the Optimizer is happy to do -- rearrange the tables in a JOIN.

Turn the query inside out. By this, I mean to do the minimum amount of work to do the main JOIN. Collect mostly id(s). Then do the dependent subqueries in an outer select. Something like:

SELECT ... ( SELECT ... ), ...
    FROM ( SELECT a1.id
               FROM a AS a1
               JOIN b ON ..
               JOIN c ON .. )
    JOIN a AS a2  ON a2.id = a1.id
    JOIN d  ON ...

The "inside-out" kludge may eliminate the need for the GROUP BY. (Your query is too complex for me to see for sure.) If so, then I call the problem "explode-implode" -- Your query first JOINs, producing a temp table with lots of rows ("explodes"). Then it does a GROUP BY ("implodes").

More

These indexes will probably help:

status:  (auftrag_id, typ, datum, aenderung)
rechnungen:  (beglichen, kunde_id, rechnung_tag)
rechnungen:  (rechnungsnummer, beglichen)
zahlungsplanneu:  (auftrag_id, rechnungsnummer)
kunden_keywords:  (kunde_id, keyword)  -- (unless `kunde_id` is the PK)

(I see from all 3 EXPLAINs that you probably have sufficient indexes on kunden_keywords and status. Show me what indexes you have, so I can see if the existing indexes are as good as my suggestions.) "Using index" == "covering index".

Near the end is this LEFT JOIN, but I did not spot any use for the table; perhaps it can be removed?

left join  `kunden_keywords` on(`angebot`.`kunde_id` = `kunden_keywords`.`kunde_id`))

edited Aug 03 '21 at 17:46

answered Jul 30 '21 at 16:20

Rick James

135,179
13
127
222

Thanks Rick for the good answer, I will rearrange the query. But I didn't understand why the query executed as query is so much faster than the same query inside a view. What is the difference for the MySQL Server? And is it possible to speed this up? I created the view as Temp Table or Merge, makes no difference in speed. – user3236231 Jul 31 '21 at 00:32
1

@user3236231 - In MySQL, `VIEWs` are just syntactic sugar. They cannot improve performance and possibly can hurt. – Rick James Jul 31 '21 at 06:09
Yes and thats the reason why I don't understand why there is such a big difference between executing the query as query (0.1 sec) and save query as view and then execute it (2.0 sek) – user3236231 Jul 31 '21 at 08:32
Alas, I have not discovered what 'type' of `SELECT` lose performance by simply encapsulating in a `VIEW`. It _may_ have to do with the `WHERE` clause that is applied to the `VIEW` failing to trickle down into the `SELECT` where it can be better optimized. Please provide the SQL for (1) the standalone, fast, Select, (2) the View, and (3) the usage of the View (slow). – Rick James Jul 31 '21 at 17:57
Thanks Rick that you take time for me to help. I updated the post with all the statements you asked for. – user3236231 Aug 03 '21 at 09:21
Which of those 3 Explains has the added WHERE clause; which was a View; what was the speed of each? (You have some of the answers, but I need each answer for each Explain.) And what WHERE clause are you referring to? – Rick James Aug 03 '21 at 17:52
Thanks for the advice with removing the unused subquery (kunden_keywords). It speeded the query from 0.18 sec -> 0.06 sec and the view from 1.86 -> 1.02 sec. Thanks a lot! I hope that I have ordered it correctly that you can understand what explain belongs to what query. – user3236231 Aug 04 '21 at 16:59
@user3236231 - When you have `PRIMARY KEY(abc)`, there is no need to add `INDEX(abc)`, since the PK already serves as an index. – Rick James Aug 04 '21 at 20:27

MySQL View 20x slower than Select

1 Answers1