Opened 14 years ago

Closed 14 years ago

Last modified 14 years ago

#7946 closed enhancement (fixed)

Add SQL indexes to crossreferenced data in MythVideo

Reported by: Raymond Wagner Owned by: robertm
Priority: minor Milestone: 0.24
Component: Plugin - MythVideo Version: head
Severity: low Keywords:
Cc: Ticket locked: no

Description

This patch adds indexes to the videometadatacast, videometadatagenre, and videometadatacountry tables. This allows searches for videos containing a list of actors to be performed in a reasonable time (1.33 seconds vs. 10min before I killed mysql). This is to allow an improvement over a search in the new Python bindings which is only capable of search of one cast member.

SELECT videometadata.*

FROM videometadata

WHERE (SELECT COUNT(DISTINCT videocast.cast)

FROM videometadatacast JOIN videocast

ON videocast.intid=videometadatacast.idcast

WHERE videometadatacast.idvideo=videometadata.intid

AND ( videocast.cast='Nathan Lane'

OR videocast.cast='Meg Gillentine')

) = 2;

Thanks to kormoc for the SQL.

Attachments (2)

mvschema.patch (904 bytes) - added by Raymond Wagner 14 years ago.
mvschema.patch2 (2.0 KB) - added by Raymond Wagner 14 years ago.
Should take care of any duplicate entries.

Download all attachments as: .zip

Change History (6)

Changed 14 years ago by Raymond Wagner

Attachment: mvschema.patch added

comment:1 Changed 14 years ago by robertm

Owner: changed from Anduin Withers to robertm
Status: newaccepted

Changed 14 years ago by Raymond Wagner

Attachment: mvschema.patch2 added

Should take care of any duplicate entries.

comment:2 Changed 14 years ago by robertm

Milestone: 0.230.24

comment:3 Changed 14 years ago by robertm

Resolution: fixed
Status: acceptedclosed

(In [24082]) Fixes #7946. Patch from Raymond Wagner to add SQL indexes to cross reference cast, genre, and country in mythvideo DB schema. Mostly relevant to upcoming improvements in the Python bindings, but also applicable to MythVideo? proper.

comment:4 Changed 14 years ago by Raymond Wagner

(In [24221]) Refs #7946. Adds ability to search for videos with multiple cast/genre/countries. Multiple values are specified as a comma-separated string.

Note: See TracTickets for help on using tickets.