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?

Improve this question
2
  • use views: dev.mysql.com/doc/refman/5.5/en/create-view.html Commented Aug 30, 2012 at 7: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. Commented Aug 30, 2012 at 8:49

2 Answers 2

Reset to default
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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Yes, it is possible with views

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

Improve this answer

3 Comments

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".
@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?
@BrankoDimitrijevic, yes I think, "pre-JOIN"ed by MySQL itself. I don't know about the Oracle.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.