Re: view/table slows down query although it is not used

From: Luuk <luuk_at_invalid.lan>
Date: Mon, 7 Nov 2016 09:58:06 +0100
Message-ID: <5820421b$0$940$e4fe514c_at_news.xs4all.nl>


On 04-11-16 09:12, kfrydrys ILIM wrote:
> Hello,
>
> We have such problem.
>
> There is one database d1 which contains many tables and several views.
> There is view v_abc which uses table abc.
> There is big query Q1 which DOES NOT use view v_abc nor table abc.
> There are no triggers on SELECT EVENT.
>
> When we run query Q1 normally it executes 16 seconds.
>
> When we clear data in table abc (TRUNCATE abc) or drop view v_abc the execution time of query Q1 is 0.01 sec !!!
>
> We have tried this on
> - RHEL 6 with MariaDB 10.1
> - Ubuntu 12.04 with MySQL 5.5
>
> Pleas help. Thank you in advance for any advices.
>

Did you use ANALYZE?
( http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html )

ANALYZE tABLE Q1;

or OPTIMIZE?
( http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html )

OPTIMIZE TABLE Q1; The last one could result in a quicker result if there has been a lot of mutations on the table. Received on Mon Nov 07 2016 - 09:58:06 CET

Original text of this message