correct primary key indexing Perhaps this is more of a DB/SQL question than Drupal. However, when defining large primary keys (multiple cols) how should I be indexing?

In my example below I am both setting a primary key -and- index on multiple cols. This is because I expect to use all of those cols in the WHERE part of queries. Is this correct?

  $schema['tournament_stats_node'] = array(
    'description' =&gt; 'Stores relations between tournament stats and node.',
    'fields' =&gt; array(    
      'entity_type' =&gt; array(
        'description' =&gt; 'Primary key: Entity type of participants, e.g. user/team.',
        'type' =&gt; 'varchar',
        'length' =&gt; 32,
        'not null' =&gt; TRUE,
        'default' =&gt; '',       
      ),
      'nid' =&gt; array(
        'description' =&gt; 'Primary key: Related node ID',
        'type' =&gt; 'int',
        'not null' =&gt; TRUE,
        'unsigned' =&gt; TRUE,
        'default' =&gt; 0,
      ),             
      'id' =&gt; array(
        'description' =&gt; 'Primary key: Unique entity ID',
        'type' =&gt; 'int',
        'unsigned' =&gt; TRUE,
        'not null' =&gt; TRUE,
        'default' =&gt; 0,
      ),
      'stat' =&gt; array(
        'description' =&gt; 'Tournament stat type',
        'type' =&gt; 'varchar',
        'length' =&gt; 32,
        'not null' =&gt; TRUE,
        'default' =&gt; '',   
      ),                                 
    ),
    'primary key' =&gt; array('entity_type', 'nid', 'id'),
    'indexes' =&gt; array(
      'entity_type' =&gt; array('entity_type'),
      'id' =&gt; array('id'),
      'nid' =&gt; array('nid'),
      'stat' =&gt; array('stat'),      
    ),
  );
This is not a trivial question and you're actually asking multiple things :)

As mentioned by BetaRide, a technical, auto-generated key has the advantage that it is much simpler to maintain. The primary key defines how the data is stored and if you have a simple key then update/insert/delete should be a bit faster. What's better in general is a hard question to answer, you could make some comparisons (test both approaches, write 1 million rows and then compare update/delete/query speed)

For the indexes, you do most certainly not need a separate key per column. Some hints:


Only a single index can be used for a single query. Unless you will have queries which only have a condition on e.g. the id, that index is useless.
Indexes on multiple columns are read from left to right. E.g. a index on A,B,C can also be used for a query with conditions on A and B but not for one on B and C.
The more indexes you have, the slower are updates.


Same here, I recommend to fill your table with data and then use EXPLAIN to see how your queries are executed and which index they use. Then you can start to optimize things.