0

By complex queries I mean queries where you have to use at least 2 joins. Let's say I have build a schema/model of the database. Now providing that I have users.user_id = 5 I'm asking for logins.lastlogin, groups.groupname, and history.user_weight. I will not write any join SQL statements, all of them will be joined at the background by the database's schema/model. Is it possible with MySQL?

ilhan
  • 8,700
  • 35
  • 117
  • 201
  • use views: http://dev.mysql.com/doc/refman/5.5/en/create-view.html – Omesh Aug 30 '12 at 07:31
  • @Omesh, but then you have to write queries into the views. Why just simply don't use it out of the view? At the and it is same as normal ones. – ilhan Aug 30 '12 at 08:49

2 Answers2

2

If you don't like the Views idea - which, btw, is ideal - you could always mimic the Oracle-materialized views Branko mentioned by doing:

Create INSERT, DELETE and UPDATE triggers on each of the tables you are joining, users, logins, groups and history ; which will update a new 5th table called lgh (example)

So you use this new lgh table for the query you wanted above. It may reduce the number of rows scanned than an actual view but there's now an overhead every time data changes in any of the original 4 tables. Depending on how often you use this new lgh table vs updating the other 4, you may see some performance improvement.

Disclaimer:

  • I don't condone this workaround, it's just an alternate suggestion for your question.
  • You should really consider adding more indexes to the original 4 tables instead, maybe even for every column you use in that join query.
  • This may resemble 'caching'...it's not quite.
Community
  • 1
  • 1
aneroid
  • 12,983
  • 3
  • 36
  • 66
1

Yes, it is possible with views

http://dev.mysql.com/doc/refman/5.5/en/views.html

sumskyi
  • 1,827
  • 13
  • 13
  • Noooo! First it processes all the data, even if the result is going to be one row. When you have 300000 rows it is pain in the a**; it says "execution time limit". – ilhan Aug 30 '12 at 07:59
  • 1
    @george What do you mean "processes all the data". Properly indexed base tables will ensure a query on a view has good performance by **not** doing full table scans, same as with out-of-view JOINs. Are you actually asking if MySQL can "pre-JOIN" the data, a-la Oracle materialized views? – Branko Dimitrijevic Aug 30 '12 at 09:28
  • @BrankoDimitrijevic, yes I think, "pre-JOIN"ed by MySQL itself. I don't know about the Oracle. – ilhan Aug 30 '12 at 10:30