1

I have multiple views in my database that I am trying to perform a JOIN on when certain queries get very complex. As a worst case I would have to join 3 views with the following stats:

  • View 1 has 60K+ rows with 26 fields.
  • View 2 has 60K+ rows with 15 fields.
  • View 3 has 80K+ rows with 8 fields.

Joining views 1 and 2 seem to be no problem, but anytime I try to join the third view the query hangs. I'm wondering if there are any best practices I should be following to keep these querys from hanging. I've tried to use the smallest fields possible (medium/small ints where possible, ect).

We are using MySQL 5.0.92 community edition with MyISAM tables. Not sure if InnoDB would be more efficient.

As a last resort I thinking of splitting the one query into two, hitting views 1 & 2 with the first query, and then view 3 separately with the 3rd. Is there any downside to this other than making 2 queries?

Thanks.

user387049
  • 6,647
  • 8
  • 53
  • 55
  • Is View 3 only slow when you join to it? What about when you query to it only? Are you joining by a column that is indexed in the underlying table? – Francisco Soto Nov 17 '11 at 17:05
  • @FranciscoSoto So looking deeper I noticed on the main underlying table of view 3 I had a primary key on two fields (id_primary, id_company). I thought that would have me covered, but per your suggestion I added a separate index to just id_company. This has the queries actually finishing, although they still take too long for my likes(5+ sec). Thanks for the help! Any other suggestions? – user387049 Nov 17 '11 at 17:35
  • I don't think I can make more suggestions without actually looking at the query and maybe what the EXPLAIN command told you. – Francisco Soto Nov 17 '11 at 18:26
  • 1
    Also, a composite index (an index with more than one column) with columns (a, b) would not help when you query only for b. It helps with a, and a + b, but not with only b. That's why the single index you added improved the situation. – Francisco Soto Nov 17 '11 at 18:29

3 Answers3

1

You need to use EXPLAIN to understand why the performance is poor.

I wouldn't think you need to worry about MyISAM vs. InnoDB for this particular read performance just yet. MyISAM versus InnoDB

Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
0

If its possible to get what how the original VIEWs are defined, then use that as a basis to create your own single query might be a better approach... Way back, another person had similar issues on their query. He needed to get back to the raw table of one such view to ensure it had proper indexes to accept the optimization of the query he was trying to perform. Remember a view is a subset of something else and does not have an index to work with. So, if you can't take advantage of an index at the root table of a view, you could see such a performance hit.

DRapp
  • 47,638
  • 12
  • 72
  • 142
0

I am going to post my comments as an answer:

1) Take a look at the EXPLAIN command and see what it says.

2) Check the performance of the individual views. Are they as fast as you think on their own?

3) The columns you are using in your WHERE or JOIN clauses, do the underlying tables have indexes that apply to them? Something to have in mind:

A composite index (an index with more than one column) with columns (a, b) would not help when you query only for b. It helps with a, and a + b, but not with only b. That's why the single index you added improved the situation

4) Are you using the all the columns and all the views? If you don't wouldn't it be simpler to take a look at the views and come up with a query instead?

Francisco Soto
  • 10,277
  • 2
  • 37
  • 46