Reply to thread

Message

I've seen slow-queries go from almost an hour to a few milliseconds after creating indexes. I'm sure the query in this case is a lot of table joins and other aspects. Adding indexes manually doesn't always work then to increase performance.

Last I looked into this myself, before aborting dynamic ones and going with a bunch of static ones instead; I had a few tables that contained a bunch of rows relating to the dynamic categogies added. The column names you mention do not sound right for those tables.

Easy to run a test on this box, and it gives me the following:

[node] table gets an entry added for "Actors" which is the dynamic filter entry added. Below that on this test box I get 32 actor node entries from the small selection of movies this test setup holds (15 movies if memory serves me, which would lead to 75 actors, but I'm testing a lot of movie series for IMDb+ which obviously results in a lot of reduced entries then).

The [parent] column of each added actor refers back to the ID that belongs to the "Actors" node entry, but they all get a unique [filter] ID.
The [filter] ID from that table gets linked to the [filter] ID used for dynamic actors inside the [node__node] table.

Just speculating now, but the delay is either from verifying that all those static entries added to the [node] table and their reference in [node__node] are still valid each time, or causes a giant delay in some crazy query that lacks the indexes to do it fast. Guess you can try to add indexex on the respective columns in those tables, but would help to find out first what query is actually used that causes the delay (easier to spot best place to add indexes then).

I can do some trial & error later this week on a large scale test database copy, by enabling dynamic actors on it and see what indexes are needed. But you will probably beat me to it.

<blockquote data-quote="RoChess" data-source="post: 973497" data-attributes="member: 18896">I've seen slow-queries go from almost an hour to a few milliseconds after creating indexes. I'm sure the query in this case is a lot of table joins and other aspects. Adding indexes manually doesn't always work then to increase performance.&nbsp;Last I looked into this myself, before aborting dynamic ones and going with a bunch of static ones instead; I had a few tables that contained a bunch of rows relating to the dynamic categogies added. The column names you mention do not sound right for those tables.&nbsp;Easy to run a test on this box, and it gives me the following:&nbsp;[node] table gets an entry added for &quot;Actors&quot; which is the dynamic filter entry added. Below that on this test box I get 32 actor node entries from the small selection of movies this test setup holds (15 movies if memory serves me, which would lead to 75 actors, but I'm testing a lot of movie series for IMDb+ which obviously results in a lot of reduced entries then).&nbsp;The [parent] column of each added actor refers back to the ID that belongs to the &quot;Actors&quot; node entry, but they all get a unique [filter] ID.The [filter] ID from that table gets linked to the [filter] ID used for dynamic actors inside the [node__node] table.&nbsp;Just speculating now, but the delay is either from verifying that all those static entries added to the [node] table and their reference in [node__node] are still valid each time, or causes a giant delay in some crazy query that lacks the indexes to do it fast. Guess you can try to add indexex on the respective columns in those tables, but would help to find out first what query is actually used that causes the delay (easier to spot best place to add indexes then).&nbsp;I can do some trial &amp; error later this week on a large scale test database copy, by enabling dynamic actors on it and see what indexes are needed. But you will probably beat me to it.</blockquote>

[QUOTE="RoChess, post: 973497, member: 18896"] I've seen slow-queries go from almost an hour to a few milliseconds after creating indexes. I'm sure the query in this case is a lot of table joins and other aspects. Adding indexes manually doesn't always work then to increase performance. Last I looked into this myself, before aborting dynamic ones and going with a bunch of static ones instead; I had a few tables that contained a bunch of rows relating to the dynamic categogies added. The column names you mention do not sound right for those tables. Easy to run a test on this box, and it gives me the following: [node] table gets an entry added for "Actors" which is the dynamic filter entry added. Below that on this test box I get 32 actor node entries from the small selection of movies this test setup holds (15 movies if memory serves me, which would lead to 75 actors, but I'm testing a lot of movie series for IMDb+ which obviously results in a lot of reduced entries then). The [parent] column of each added actor refers back to the ID that belongs to the "Actors" node entry, but they all get a unique [filter] ID. The [filter] ID from that table gets linked to the [filter] ID used for dynamic actors inside the [node__node] table. Just speculating now, but the delay is either from verifying that all those static entries added to the [node] table and their reference in [node__node] are still valid each time, or causes a giant delay in some crazy query that lacks the indexes to do it fast. Guess you can try to add indexex on the respective columns in those tables, but would help to find out first what query is actually used that causes the delay (easier to spot best place to add indexes then). I can do some trial & error later this week on a large scale test database copy, by enabling dynamic actors on it and see what indexes are needed. But you will probably beat me to it. [/QUOTE]