InnoDB tables?

  • Carlos García Montoro

    Has anyone used InnoDB tables for refbase?

    I have to do some customization to refbase. A foreign key would be useful for me, but since tables are MyISAM, FK are unsupported. Has anyone previous experiences? Any problems or performance issues?

    Thanking you in advance,

  • Richard Karnesky

    So far, AFAIK, we haven't taken advantage of any features peculiar to MyISAM (though we might wish to at a later date (particularly full text search)). InnoDB should be stable, but I haven't tested it. Performance of MyISAM is typically better & there are still a few SELECT queries that can be somewhat slow on very large databases, so I'd want to test a reasonable sized setup first. I can potentially mirror a large dataset onto InnoDB for testing if it'd be helpful, but don't know when I could get around to this. We've taken the approach of having "simulated foreign keys" (i.e. manually making references between tables and handling constraints in PHP) in part to maximize the number of setups refbase will work with. Depending on the extent of your customization & your desire to have your changes potentially adopted by us, you may wish to do something similar.


  • Carlos García Montoro

    Thank you for the answer.

    We are a research institution and I have to classify our references (the one that we do) by the research groups and the financial founds that made them possible. Both fields, groups and financial founds, are known lists, i.e. I have the list of research groups and the list of founders. Occasionally one new item (new group or new founder) would be created. For each reference made by our institution I should store a list of groups and a list of founders, since more than one is possible. I also have to be able to search by both fields (this fields should be considered when simple search, advanced search, and so on). I can handle both fields in the same way, so, since now I'll write about only one of them: groups. I was considering different ways to do this possible:

    First, when adding a record, I'll provide a multiple selection listbox. An alternative would be checkboxes, but there would be too many… But, when storing values in the database, I have considered this solutions:

    1/ Creating a new column on table refs and using a "set" of MySQL to store the list of groups. Easy to handle, but limited to 64 groups. Currently we have about 30 groups or founders, but as time goes on, this limitation could be a problem.

    2/ The relational way: Make a table with groups (with ids) and make a table that relates references and groups using two foreign keys (either real FK or simulated). This is a nice and elegant approach, but I'm affraid about the code that should create the SQL query when searching (simple search, advanced search, SQL search,…) I don't still know if the JOINs would be difficult to construct, if this requires a big revision of the *search*.php files and the

    3/ "A la refbase": Conceptually, research groups are similar to "My own groups" with the difference that "research groups" is a field of the reference rather than a per user field. I was considering to store literals and some character to separate values in a new (group) field on table refs, and from php implode/explode it. refbase people do it to store and retrieve "My own groups" when searching, hence, it shouldn't be too complicate since something similar is already done, but I'm affraid about the size of the database and the performance of searches. Another reason that prevents me to do this is that while "my own groups" may take any value, the values of "research groups" are previously known, hence there are more efficient ways to save this information than a varchar or text…

    Any suggestion is welcome,

  • Richard Karnesky

    Indeed, solution 3 might be getting similar to the public groups/tagging features that we'd like to implement.  Depending on the way this was written & how general it was, there is a reasonable chance for getting it mainlined.

    I suspect that solution 2 is probably the fastest to implement & actually also mirrors the way that user data works in refbase.  U'd use simulated FK.  I would suspect that this data would not be essential in all views, so that you can add JOIN statements as-needed (a mixed blessing, actually: if public tagging is implemented, your additional JOINs may be slower than the official refbase & you may think of ways to keep the ability to convert your data.  But you may also have JOINs in fewer places than we'd expect for actual tagging support, so it'd be faster than real tagging).  The principle downside is that this isn't generic & is much less likely to get mainlined.

    I'd probably be inclined to use MyISAM for speed and compatibility in any case:  real foreign keys do buy you a bit in data stability, but not so much that it is worth the bother in this relatively limited case.

    Please let us know if you'd like to join the developer mailing list (very light traffic, as development happens in spurts) and/or need design help.  This goes double if you intend to make public groups/tagging that are generic enough to be added to refbase proper!


  • Carlos García Montoro

    <p>Rick, thank you again for your answer.</p>

    <p>I'd like to join the developer mailing list, although I don't really know <b>if</b> and <b>how much</b> I'd be able to contribute. In anycase, I have to develop some code, so stay in contact with developers will be positive for us.</p>

    <p>With respect to my previous question, having read your answer, and having collected more info about what I have to do, I think that I'm going to try <i>2/ The relational way</i>. I have been told that groups and funders may have a life cycle. When creating a reference they don't all groups or funders be listed, but only those that are currently active. On the other hand, one should be able to look for references of any funder, no matter if it is active or no. Hence, I think that the easiest way to handle with this, is to save it in the database, with a BOOL column in the table of funders.</p>

    <p>Since I have some time constraints, I don't know if and how generic I'll be able to be. Nor do I know if our development will be helpful to others. At the moment, I'll not include this new fields in the views. Only when one sees or creates a reference these fields will be shown. Search pages will also be ready to search references with some conditions on these fields, and at the moment, these fields wouldn't be shown in the results pages of searches.</p>

    <p>Thank you again,

  • Carlos García Montoro

    Sorry, I haven't found a way to post here a message with a minimal decent readable format :-(

  • Richard Karnesky

    Sounds good.  You have been subscribed to the dev list with your sourceforge email list.  Contact me (off-of-this forum to prevent spam) if you wanted an alternate address & can't figure out how to change it.


Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

No, thanks