[development] Is it possible to have separate table for different content types in D7

Fri Feb 11 21:09:21 UTC 2011

Also, let me add Fourth, how is that even possible?  Computers take cycles to complete operations.  More operations mean more cycles, so while it may be a very small performance impact, as long as the server needs to do more logic, it is going to have a performance impact.

Sam Tresler

On Fri, 11 Feb 2011, Sam Tresler wrote:

> "Joins on properly indexed tables (such as nodes + fields) don't have any 
> serious performance impact."
>
> This is a phrase in one form or another that I've heard several times on this 
> mailing list.  It's one of those things that is textbook true, but in 
> practice has quite a few implications.
>
> First, Drupal does as good a job as it can indexing tables, but it is 
> impossible for Drupal to know what content type a user will be building with 
> Drupal.  The more tables involved, the more indexes will be needed to 
> retrieve the information desired.  So, true, a properly indexed database will 
> not suffer greatly from joins, but the amount of time spent setting up the 
> proper indexes for a developer is directly proportional to the number of 
> tables involved in a query.
>
> Second, the mysql preprocessor does the best job it can deciding which 
> indexes to use for which tables.  It fails frequently.  Again, more tables 
> makes the logic for mysql take that much longer.  Technically, the joins 
> aren't taking the time, but the performance is still suffering as a result of 
> the number of tables involved.
>
> Third, do we want a core that is only performant to people who understand 
> mysql indexing?
>
> -Sam Tresler
>
>
> On Fri, 11 Feb 2011, Jamie Holly wrote:
>
>> Joins on properly indexed tables (such as nodes + fields) don't have any 
>> serious performance impact. It' the very basis of a relational database, so 
>> they are made to handle them optimally.
>> 
>> One thing to consider with your multiple table approach. What if you want a 
>> page listing all content on the site, ordered by create date? You are going 
>> to have to get into a very serious query and it will force the database to 
>> use filesort and temporary tables. There's no getting around that when 
>> sorting by a column distributed amongst multiple tables. This performance 
>> impact will be thousands times worse than having all your content types in 
>> the single node table.
>> 
>> Another issue is keeping unique nids (though not that complex to work out). 
>> In Drupal, the nid is an auto increment field. You would want to manage the 
>> new nids on your own so that two different content types don't have the 
>> same NID. If they do, you would severely break other modules, like 
>> comments.
>> 
>> From an overall management stand point, I would keep all the nodes in the 
>> same table and look at better caching solutions. If your going to be 
>> getting mostly anonymous visitors, then something like Boost (or Varnish if 
>> this is a VPS or dedicated server) will give you far more performance gains 
>> than anything else.
>> 
>> Jamie Holly
>> http://www.intoxination.net
>> http://www.hollyit.net
>> 
>> 
>> On 2/11/2011 12:00 PM, Deva wrote:
>>> In my case i am going to have lots of content types with lots of cck 
>>> fields.
>>> D7 creates new table for each cck field. So while reading a node from 
>>> database. It is going to do a join. I dont think that is good thing.
>>> 
>>> That is why i was checking if it is possible to have different table for 
>>> each content type.
>>> 
>>> 
>>> On Fri, Feb 11, 2011 at 9:48 PM, nan wich <nan_wich at bellsouth.net 
>>> <mailto:nan_wich at bellsouth.net>> wrote:
>>>
>>>     Blake, I totally agree. When I created my first node module, even
>>>     the docs you mention didn't exist. I had to look at other modules
>>>     to get an idea of how to do it. I even had to do the same with
>>>     adding fields to Views.
>>>     "...unless what they offer is documented, in a manner that others
>>>     can recreate... it may as well not exist" is absolutely true.
>>>     However, one must also realize that one form of documentation may
>>>     not "fit all." For example, I really appreciate all the Views docs
>>>     that exist now, but most are written way over my head - and I am
>>>     no beginner with Drupal. I fully accept that my failure to grasp
>>>     those docs are my fault, but I have heard many others express the
>>>     same feeling, while, at the same time, I see others just glance at
>>>     it and turn out perfect code right away. Different people learn in
>>>     different ways. I guess that's why there are many books.
>>>
>>>     /*Nancy*/
>>>
>>>     Injustice anywhere is a threat to justice everywhere. -- Dr.
>>>     Martin L. King, Jr.
>>> 
>>> 
>>>
>>>     ------------------------------------------------------------------------
>>>     *From:* Blake Senftner <bsenftner at earthlink.net
>>>     <mailto:bsenftner at earthlink.net>>
>>>     *To:* development at drupal.org <mailto:development at drupal.org>
>>>     *Sent:* Fri, February 11, 2011 10:45:33 AM
>>>     *Subject:* Re: [development] Is it possible to have separate table
>>>     for different content types in D7
>>>
>>>     Granted, I've not yet dived into the D7 specifics of creating
>>>     custom content types, but I'd like to address a point Nancy makes
>>>     here:
>>>
>>>     Lastly, why? I would think that the overhead of managing multiple
>>>     tables would outweigh any potential gains. I can't even begin to
>>>     think what you would have to do to Views to make it work in your
>>>     scenario.
>>>
>>>     I consider it an issue of quality documentation. When I was first
>>>     learning module development, CCK was nice, but I could not figure
>>>     out how to programmatically create or manage CCK fields.  Being
>>>     unable to programmatically create content types with CCK meant
>>>     that my modules either could not implement content types, or I'd
>>>     have to make them without CCK. There was no quality documentation
>>>     explaining CCK at the time, so via books like "Front End Drupal"
>>>     and "Pro Drupal Module Development" I learned how to create my own
>>>     tables and manage them myself, including the
>>>     not-difficult-because-it's-documented integration of custom fields
>>>     with Views. (see:
>>>     http://views-help.doc.logrus.com/help/views/api-tables).
>>>
>>>     I absolutely do not mean to pick on Nancy. I love Drupal. I'm
>>>     betting my company on the Drupal technology stack. But developers
>>>     have got to realize that unless what they offer is documented, in
>>>     a manner that others can recreate and expand on your module's
>>>     facilities, /it may as well not exist/. Poor or missing
>>>     documentation leads to poor, incorrect, or missing integration
>>>     with other modules. (Sorry if this sounds like a rant. Trying to
>>>     figure out things in Drupal is a sore spot for me.)
>>>
>>>     Sincerely,
>>>     -Blake
>>>     bsenftner at earthlink.net <mailto:bsenftner at earthlink.net>
>>>     www.BlakeSenftner.com <http://www.blakesenftner.com/>
>>> 
>>>
>>>     On Feb 11, 2011, at 7:16 AM, nan wich wrote:
>>>
>>>>     There are several extra questions to be asked here:
>>>>
>>>>        1. Define "separate." If one creates a node module that
>>>>           creates content types, then one must manage the extra
>>>>           fields - generally in new (i.e. separated from node &
>>>>           node_revisions) tables created by the module.
>>>>        2. If one is talking about content created by other (e.g.
>>>>           core) modules, then the answer is maybe. Take a look at the
>>>>           sql rewriting hooks or whatever D7 has done to them.
>>>>        3. Lastly, why? I would think that the overhead of managing
>>>>           multiple tables would outweigh any potential gains. I can't
>>>>           even begin to think what you would have to do to Views to
>>>>           make it work in your scenario.
>>>>
>>>>     /*Nancy*/
>>>> 
>>>>
>>>>     Injustice anywhere is a threat to justice everywhere. -- Dr.
>>>>     Martin L. King, Jr.
>>>> 
>>>> 
>>>>
>>>>     ------------------------------------------------------------------------
>>>>     *From:*Deva <devendra.in at gmail.com <mailto:devendra.in at gmail.com>>
>>>>     *To:*development at drupal.org <mailto:development at drupal.org>
>>>>     *Sent:*Fri, February 11, 2011 7:59:26 AM
>>>>     *Subject:*[development] Is it possible to have separate table for
>>>>     different content types in D7
>>>>
>>>>     Hi All,
>>>>
>>>>     Is possible to have separate table for each content type?
>>>>
>>>>     Thanks in advance
>>>>
>>>>     --
>>>>     :DJ
>>>> 
>>>> 
>>> 
>>> 
>>> 
>>> 
>>> -- 
>>> :DJ
>>> 
>>> 
>> 
>