Modify

Opened 21 months ago

Last modified 10 months ago

#12592 new Developer Task

maintanence work on database schema - move to InnoDB and use referential integrity

Reported by: dekarl Owned by: dekarl
Priority: minor Milestone: 29.0
Component: MythTV - General Version: Unspecified
Severity: medium Keywords: schemachange
Cc: Ticket locked: no

Description

Random notes around the seektable, this is something I wanted for 0.28 but I'm not getting it ready and tested by tomorrow, so it has to wait until after the branch point of fixes/0.28.

  • spread recordedid around and have it replace chanid+starttime in the tables (includes some work on access functions / Service API, so they can still work with chanid+starttime)
  • work out the defaults that we have to override to get similar results across various versions of MySQL (https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html file-per-table vs. all tables in one file changed in 5.6.6)
  • switch the small tables to InnoDB
  • enable referential integrity and fix all consistency bugs lurking around
  • switch the big tables (seek table) to InnoDB, choosing the primary key to optimize the data locality
  • reorder columns in tables / indexes (for data locality)
  • decide if the should mix the storage location of tables (e.g. all small ones into the system tablespace and the big ones into their own files)

If someone has a (partial) implementation in their private repo, feel free to just grab this tracking ticket.

Attachments (0)

Change History (4)

comment:1 Changed 18 months ago by stuarta

  • Milestone changed from 0.29 to 29.0

Milestone renamed

comment:2 Changed 18 months ago by Gary Buhrmaster <gary.buhrmaster@…>

In 3865f57c1aeb7fd7615a6143357aa41fe36f43b9/mythtv:

Update tables related to the program table

In the case that a program is deleted or changed, insure
that the related programrating and programgenres tables
are also modified appropriately (as is the credits table)

Fixes #12729
Refs #12592

(cherry picked from commit 74a1b214a838a7e2429da6eb89758eef20d1f963)
Signed-off-by: Karl Dietz <dekarl@…>

comment:3 Changed 16 months ago by dekarl

Here's the plan as it was 5 years ago TaskRecordedFile

comment:4 Changed 10 months ago by dekarl

  • merge videocast into people (which could use some housekeeping, people and credits have ~160k entries each on my system) and videometadatacast into credits.
  • convert filemarkup from filename as PK to reference videometadata by intid (file system browse mode must die, or at least get by without bookmarks)

Add Comment

Modify Ticket

Action
as new The owner will remain dekarl.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.