I see there are no indexes and primary key set for draggableviews_structure table.
I think the weight and entity_id fields could definitely use indexing and maybe some kind of multi field primary key as well?

CommentFileSizeAuthor
#5 draggableviews-performance-indexes-7.x-2.0-1432456-5-do-not-test.patch911 bytesDavid_Rothstein
#5 draggableviews-performance-indexes-1432456-5.patch1 KBDavid_Rothstein

Comments

ygerasimov’s picture

ygerasimov commented
Priority: Normal » Minor

There is primary key 'dvid' in draggableviews_structure table. See http://drupalcode.org/project/draggableviews.git/blob/refs/heads/7.x-2.x...

Please advise what indexes you would like to add? Have you faced any performance issues with module so I can emulate this case and test adding new indexes to improve?

philipz’s picture

philipz commented

I will be testing performance adding indexes to weight field this week as my database gets bigger.

The strange thing is I can see there is the 'dvid' primary key definition in .install file but I have no such field in my draggableviews_structure table and there is no primary key either.

The other question is would it be better to use
'primary key' => array('view_name', 'view_display', 'args', 'entity_id')
as the 6.x-3.x does?

ygerasimov’s picture

ygerasimov commented

@philipz, I have added dvid field quite recently so this might be a reason why you don't have this field.

I wouldn't go for index just because it is used in 6.x-3.x branch as 7.2 is complete rewrite and has different database structure. Lets have practical performance issue with index and then solve it.

ygerasimov’s picture

ygerasimov commented
Status: Active » Closed (works as designed)

Please reopen this issue if have performance issue that can be solved with proposal from #2.

David_Rothstein’s picture

David_Rothstein commented
Title: Performance with primary key and indexes » Improve performance when {draggableviews_structure} has a large number of rows by adding indexes
Priority: Minor » Major
Status: Closed (works as designed) » Needs review
StatusFileSize
new draggableviews-performance-indexes-1432456-5.patch1 KB
new draggableviews-performance-indexes-7.x-2.0-1432456-5-do-not-test.patch911 bytes

I hit major performance issues on a site with around 26,000 rows in the {draggableviews_structure} table. Views that were using draggable views were generating database queries that took minutes to run.

Adding indexes (but not primary keys, since the table already has a good one) along the lines of what was suggested above so far seems to have improved performance dramatically.

I'm attaching a patch against 7.x-2.x but also one against 7.x-2.0 (which I was running). I wrote the 7.x-2.x patch (which is the one up for review) so that it's harmless to run even if the first patch was previously applied.

David_Rothstein’s picture

David_Rothstein commented
Title: Improve performance when {draggableviews_structure} has a large number of rows by adding indexes » Improve performance when {draggableviews_structure} has a large number of rows by adding database indexes
David_Rothstein’s picture

David_Rothstein commented
Priority: Major » Normal

For what it's worth, though, this site is also running the patch at #1836068: Add views relationship handler to support multiple sorts and I think it's possible that some of the performance hit comes from that.

Tentatively lowering priority because of that, but I think either way it's pretty reasonable to add these indexes.

ygerasimov’s picture

ygerasimov commented
Status: Needs review » Fixed

Commited. Thank you for this improvement.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

istryker’s picture

istryker commented

This patch causes MySQL problems from versions 5.5.13 and older, which uses utf8 charset. This is because of the size limitation of the index to 1000 bytes.

utf8 characters = 3 bytes.
latin1 characters = 1 byte.

view index is 447 chacter * 3 bytes/chacter = 1341 bytes

Not going to reopen this issue. See #1910904: Enabling Dev version shows error

geerlingguy’s picture

geerlingguy commented

See related: #2104791: Add simple index for entity_id to draggableviews_structure.