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

W dniu piątek, 4 listopada 2016 10:14:27 UTC+1 użytkownik The Natural Philosopher napisał:
> On 04/11/16 08: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.
> >
> >
> > Best regards,
> > KFrydrys
> >
> >
> >
> Are you SURE that you are not getting a cached result from Q1?
> i.e. if you put everything back the way it was and run Q1 *twice* does
> it still take 16 seconds?
>
>

[Quoted] Thank you. After investigation i am sure that execution of Q1 is very slow, and the next ones are very quick, until i make "reset query cache". [Quoted] On the other hand i compared two databases - recent and from month ago. On the database from month ago query Q1 always executes quickly (less than 1 s). I do not understand this because the amount of records in tables used by query Q1 is almost the same. There is much more data in table abc, but it is not used by Q1. Received on Mon Nov 07 2016 - 09:25:40 CET