[SOLVED] SQL Concrete Vs Class Table Inheritance query speed on one subtype

SQL Concrete Vs Class Table Inheritance query speed on one subtype

I am weighing up between Concrete and Class Table Inheritance (see example below). Class Table certainly has a lot of benefits, in particular for my scenario, super table columns are guaranteed consistent across the full data set. However I have next to no need to query every subclass at once, instead all queries will be on one subclass at a time (of which there are at least 9 subclasses).

Thus with my row count looking like it will be very large, would it be much sizable quicker to query one Concrete table with less rows ie: (as per example below)

SELECT property_address
FROM policies_property
ORDER BY date_issued DESC;

Or would the foreign key relationships be effectively quick enough that any query speed difference in looking up the very large super table is negligible in Class Table Inheritance: (as per example below)

SELECT property_address
FROM policies_property INNER JOIN policies_super ON policies_property.id = policies_super.id
ORDER BY policies_super.date_issued DESC;

An Example of Concrete Inheritance: A completely seperate table for each type with columns in common repeated in each table>

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+

--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |   
+------+---------------------+------------------+

An Example of Class Table Inheritance: One super class, many sub classes. Each subclass id references a superclass id.

--// Table: policies_super
+------+---------------------+
| id   | date_issued         |
+------+---------------------+
|    1 | 2010-08-20 12:00:00 |
|    2 | 2010-08-20 13:00:00 |
|    3 | 2010-08-20 14:00:00 | 
|    4 | 2010-08-20 15:00:00 |
+------+---------------------+

--// Table: policies_motor
+------+----------------+
| id   | vehicle_reg_no |
+------+----------------+
|    1 | 01-A-04004     |
|    2 | 02-B-01010     |
|    4 | 03-C-02020     |
+------+----------------+

--// Table: policies_property    
+------+------------------+
| id   | property_address |
+------+------------------+
|    3 | Oxford Street    |   
+------+------------------+

Solution

I have next to no need to query every subclass at once, instead all queries will be on one subclass at a time (of which there are at least 9 subclasses).

To me this sounds a convincing reason to keep them separate. Don't think the 'superclass' approach is 'better normalized' or 'more relational' etc. They are simply two design choices that are equally valid in theory; go with the one that makes most sense in practice.