Dpl642 index long read qc results

sanger / unified_warehouse

MIT License

2 stars 8 forks source link

Dpl642 index long read qc results #541

Closed emrojo closed 1 year ago

emrojo commented 1 year ago

Closes #

Changes proposed in this pull request:

Add indexes to support joining with samples table

Instructions for Reviewers

[All PRs] - Confirm PR template filled
[Feature Branches] - Review code
[Production Merges to main]
- Check story numbers included
- Check version

yoldas commented 1 year ago

Migration succeeds, but we need to set a default collation with the existing database collation to avoid mismatch while running the queries. I am investigating.

yoldas commented 1 year ago

If the query is executed after a database reset, we receive an error. We need to check what defaults are used for collation.

mysql> explain select * from sample  left outer join long_read_qc_result as lr_shearing_qc_comments  on lr_shearing_qc_comments.sample_id=sample.supplier_name and lr_shearing_qc_comments.assay_type_key='shearing_qc_comments';
ERROR 1267 (HY000): Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '='