Rather than

SELECT a.pk, b.pk, c.pk, d.name

JOIN b on b.pk = a.fk
JOIN c on c.pk = b.fk
JOIN d on d.pk = c.fk

I can do

SELECT a_pk, b_pk, c_pk, d_name

FROM view_a_b_c_d

I have a lot of this sort of thing through my code.

I've done a performance test, and the differences seem to be negligible, and I feel it would greatly tidy my codebase up and remove a lot of repetition.

But before I commit to that (as it'd be a big change with a lot of work and testing), I want to check that this IS a good thing to do. I didn't study computer science and have no formal DBA training. I'm also a sole dev working on my own closed-source product. So I don't get much input from the outside world, unless I strike out and ask for it.

Thank you - any opinions/experience appreciated.

  • In some situations there could be a performance degradation. Otherwise, Views should like a good idea for you. If any of those are 1:1 relationships, then rethink that. If you are using an EAV schema, that is problematic (with or without Views). – Rick James Jan 9 at 17:38
  • Newer versions of mysql, mariadb seems to handle views much better than what used to be the case. Older versions had problems pushing predicates through the view to the Base tables. What version are you using? – Lennart Jan 9 at 18:22
  • I'm on the latest stable release 👍 – Codemonkey Jan 9 at 19:28
  • No @Rick James, I think my schema is ok. Example would be events joined to organisers, venues and event series. – Codemonkey Jan 9 at 19:30
  • @Codemonkey -- Latest stable release of which product? Lennart is pointing out a significant difference in how JOINs are optimized between MySQL (even the latest version) and recent versions of MariaDB. Or maybe you should remove the mysql tag to avoid this confusion. – Rick James Jan 9 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.