correct primary key indexing

Question

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' => 'Stores relations between tournament stats and node.',
    'fields' => array(    
      'entity_type' => array(
        'description' => 'Primary key: Entity type of participants, e.g. user/team.',
        'type' => 'varchar',
        'length' => 32,
        'not null' => TRUE,
        'default' => '',       
      ),
      'nid' => array(
        'description' => 'Primary key: Related node ID',
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),             
      'id' => array(
        'description' => 'Primary key: Unique entity ID',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'stat' => array(
        'description' => 'Tournament stat type',
        'type' => 'varchar',
        'length' => 32,
        'not null' => TRUE,
        'default' => '',   
      ),                                 
    ),
    'primary key' => array('entity_type', 'nid', 'id'),
    'indexes' => array(
      'entity_type' => array('entity_type'),
      'id' => array('id'),
      'nid' => array('nid'),
      'stat' => array('stat'),      
    ),
  );    

Answer 1

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.

Answer 2

There's no reason to put fields in the primary key, just because you want to use a combination of these fields in queries. If the query is slow, use a normal index. Generally primary keys across multiple columns do ask for all sorts of trouble.