0

I have a problem with my MySQL database. I got an expensive query with some joins. But i run it always for one specific id, which makes the execution very fast.

Now, i put this query into a view. If i query this view and use the where clause with the id on the view, it seems as if MySQL at first loads all records and after that applies my where clause. This results in a very bad performance.

Is there a possibility to let MySQL use also my where clauses in the view before querying all records?

Thanks a lot and cheers, Argonitas

moritz.vieli
  • 1,747
  • 1
  • 14
  • 17
  • Can you post your query? We can only guess without seeing the code. – scunliffe Oct 04 '14 at 11:20
  • It works with all queries which use an id. For example: select * from tab1 left join tab2 on tab1.id = tab2.tab1_id where tab1.some_id = 2; is fast. but this one is slow: create or replace view test_v as select * from tab1 left join tab2 on tab1.id = tab2.tab1_id; select * from test_v where some_id = 2; – moritz.vieli Oct 04 '14 at 11:50
  • Hmm it looks like MySQL doesn't let you add indexes to a view like SQLServer or Oracle http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html normally an indexed view will perform very well but in MySQL it appears to act like a derived temporary table and not provide any special indexing abilities.do you have any flexibility to create another table?... And/or do you need the view or were you just trying to simplify the subsequent queries? – scunliffe Oct 04 '14 at 12:36
  • That all said there may be some options based on this answer: http://stackoverflow.com/a/13945079/6144 – scunliffe Oct 04 '14 at 12:38
  • Well, thanks scunliffe! This thread exactly describes the problem. Haven't found it, although i searched a few hours for similar issues... – moritz.vieli Oct 04 '14 at 12:46
  • Related [link](http://stackoverflow.com/questions/25386986). – crokusek Nov 17 '14 at 19:21

0 Answers0