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)
Change History (6)
Changed 14 years ago by
Attachment: | mvschema.patch added |
---|
comment:1 Changed 14 years ago by
Owner: | changed from Anduin Withers to robertm |
---|---|
Status: | new → accepted |
Changed 14 years ago by
Attachment: | mvschema.patch2 added |
---|
comment:2 Changed 14 years ago by
Milestone: | 0.23 → 0.24 |
---|
comment:3 Changed 14 years ago by
Resolution: | → fixed |
---|---|
Status: | accepted → closed |
(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.
Should take care of any duplicate entries.