3

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields.

I have the following too:

LDAPIMPORTANT - view that stores all 75K but only 15 fields

LDAPSHORT - view that stores all 75k but 5 fields

LDAPAB - view that only stores 9k employees based on two groups (field lookup)

Each of these are used a lot and for different apps and also there are a lot of views written against these views. But there is no updates to them. We do not update employee data. It is just LDAPALL update once a night.

In this circumstance should I create tables from the LDAPALL table instead of views? I could set up jobs to create these tables once a night. What is best practice behind this? Speak in layman's terms because I am a PHP developer made to do all DB admin stuff.

Improve this question
11
  • 1
    Are you having specific problems? If so, you should add that info to your question. As it is, your question is likely to be closed as "too broad" or "unclear what you're asking"
    – Hannah Vernon ♦
    Commented Aug 7, 2013 at 18:52
  • Well right now we have a small group of admins using tools to run reports. Some of the views take a while to populate and I am really worried about how this will hold up under pressure. So I am really wondering given a static table of this size, do I have a lot to gain creating sub tables instead of views.
    – LOSTinDB
    Commented Aug 7, 2013 at 18:54
  • 2
    75k rows seems tiny. How wide is each row? Do these rows include XML data or binary data (large items)? What kind of machine is running the database? Are there other databases served by this machine? Does the machine perform other services?
    – Hannah Vernon ♦
    Commented Aug 7, 2013 at 18:57
  • 1
    @LOSTinDB When you say you have 10-11 columns indexed, is this 10-11 indexes with one column each? You have to understand how indexes work. Typically, only one will be used and then key lookups are used to get the remaining data. So if you have an index on birthdate and that appears to be the most selective, that will be used and everything after that will be a scan. But if you have one on (birthdate, status) and the status is also in the query, then the index may require less scanning.
    – Cade Roux
    Commented Aug 7, 2013 at 19:22
  • 2
    @LOSTinDB It depends on your query - and really on your overall workload if you have a wide variety of queries. Ideally you want your index to be covering so that only the index is used. In MySQL there are no such thing as INCLUDEed columns, so any column requested would need to be in the key for the index to become covering.
    – Cade Roux
    Commented Aug 7, 2013 at 19:30

3 Answers 3

Reset to default
4

I would simply use the views if they perform. No need to make copies of data unnecessarily, and many database platforms allow indexed or materialized views and filtered views etc.

HOWEVER, MySQL does not support indexed views. So unless the underlying indexes on your table support the various ways you are accessing the data, it might be worthwhile materializing a version of the view yourself.

Improve this answer
6
  • That is what I noticed. Once I indexed LDAPALL it was flying. The smaller views are much slower than my big table.
    – LOSTinDB
    Commented Aug 7, 2013 at 18:56
  • I agree with Cade's suggestion to materialize the slower views using some type of automated system. This means after the LDAP data is imported, your code would create copies of the data in the format currently used by your views. Then instead of querying the views, you'd query the alternate copies instead. You need to ensure your MySQL daemon has as much CPU, memory and disk speed as it needs, otherwise you'll battle that no matter what you do.
    – Hannah Vernon ♦
    Commented Aug 7, 2013 at 19:11
  • @LOSTinDB It depends on what the views do and how they are used. The current choices of indexes on the base table may not support the views well, or there could be optimizer issues.
    – Cade Roux
    Commented Aug 7, 2013 at 19:12
  • @CadeRoux The views are there to set up subviews that will combine vendor reports with the view data to provide more in-depth analysis of the vendor report. Sometimes they are also used to do large employee filtering or searches. For example we have admins that do searches or use the emp. tree to get distribution lists for programs and stuff. Some of the searches take 20-30 seconds to come back right now.
    – LOSTinDB
    Commented Aug 7, 2013 at 19:15
  • 2
    @LOSTinDB But do the indexes actually support those queries? Or do you just end up with table scans? Take one of those queries that takes 30 seconds and look at the execution plan using EXPLAIN.
    – Cade Roux
    Commented Aug 7, 2013 at 19:18
3

The concept of replacing a view with a table that contains the same data (now duplicated) is called a materialized view.
The answer to your question might be Here

Improve this answer
2
  • So for example what would be the performance gains using something like flewviews vs creating jobs to create the various tables?
    – LOSTinDB
    Commented Aug 7, 2013 at 19:56
  • Administration would be simpler, as updating the views would be handled for you. The repopulation process would be much slower, but that's probably trivial compared to all the reads against the table.
    – Jon of All Trades
    Commented Aug 7, 2013 at 20:39
0

I gave a +1 to the materialized views. If this was something that was more real-time that is the way to go. But the fact that I only have one update a day on the user population it was just a lot of work for little reward.

I ended up setting up a set of queries that are scheduled to create the "sub-tables" which I found much more efficient than making the views. We have about 10 sub-tables that support everything. Most of these take about 2-4 seconds to create and they are spread out over 20 mins during the night.

We do have a lot of views created created off of these sub-tables and everything is running very fast compared to making tons of views off of the one big table - which just had a lot of data we didn't use/need.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.