How does subpartitioning actually work and what physical files are created? I can't understand how the subpartitioning works in mysql.
I am reading the following example:  

create table invoices (  
customer_id int not null,    
product_id int not null,    
amount decimal (8,2) not null,    
entry_date date not null default '2006-01-01',   
store_id int not null   
)  
partition by range (year(entry_date))  
subpartition by hash(customer_id)  
subpartitions 4 (  
partition p0 values less than (2008),   
partition p1 values less than (2009),   
partition p2 values less than (2010),  
partition p3 values less than (2011),  
partition plast values less than MAXVALUE  
);  


I can not understand or visualize how this is formed in the HD.
I think that 4 * 5 = 20 physical files are created. Is this correct?
And what data are stored together? Is there a "clustering" of 2008 dates for all customers with the same hash? I am confused on that. Also I am confused how the data are fetched.
Is the customer_id hash used first to determine the range partition or the opposite. What are the exact steps to fetch the data?   

UPDATE
Another example. I did the following:  

mysql&gt; create table nums_composite (  
    -&gt; id int, happened datetime not null,  
    -&gt; primary key (id, happened)  
    -&gt; ) engine=InnoDB   
    -&gt; partition by range (hour(happened))  
    -&gt; subpartition by hash(id) subpartitions 2  
    -&gt; (  
    -&gt; partition p0 values less than (10),  
    -&gt; partition p1 values less than (20),  
    -&gt; partition p2 values less than MAXVALUE  
    -&gt; );  
Query OK, 0 rows affected (0.28 sec)  


mysql&gt; select partition_name, subpartition_name as sub, partition_method as method,   partition_description ,table_rows from information_schema.partitions where table_name='nums_composite';  
+----------------+-------+--------+-----------------------+------------+  
| partition_name | sub   | method | partition_description | table_rows |  
+----------------+-------+--------+-----------------------+------------+  
| p0             | p0sp0 | RANGE  | 10                    |         17 |  
| p0             | p0sp1 | RANGE  | 10                    |         24 |  
| p1             | p1sp0 | RANGE  | 20                    |         20 |  
| p1             | p1sp1 | RANGE  | 20                    |         17 |  
| p2             | p2sp0 | RANGE  | MAXVALUE              |         13 |  
| p2             | p2sp1 | RANGE  | MAXVALUE              |          9 |  
+----------------+-------+--------+-----------------------+------------+  
6 rows in set (0.00 sec)  


It seems that I am right it creates hash_no* subpartition files but where are they?  

root@jim-Linux:/# find . -iname "*_composite*"  
./var/lib/mysql/partioning_chapter_test/nums_composite.frm  
./var/lib/mysql/partioning_chapter_test/nums_composite.par   


Why can't I see the actual files?
Sample in Windows

I ran the following on my Laptop in MySQL 5.6.15

mysql&gt; use test
Database changed
mysql&gt; drop table if exists nums_composite;
Query OK, 0 rows affected (0.70 sec)

mysql&gt; create table nums_composite (
    -&gt; id int, happened datetime not null,
    -&gt; primary key (id, happened)
    -&gt; ) engine=InnoDB
    -&gt; partition by range (hour(happened))
    -&gt; subpartition by hash(id) subpartitions 2
    -&gt; (partition p0 values less than (10),
    -&gt; partition p1 values less than (20),
    -&gt; partition p2 values less than MAXVALUE);
Query OK, 0 rows affected (1.91 sec)

mysql&gt; show create table nums_composite\G
*************************** 1. row ***************************
       Table: nums_composite
Create Table: CREATE TABLE `nums_composite` (
  `id` int(11) NOT NULL DEFAULT '0',
  `happened` datetime NOT NULL,
  PRIMARY KEY (`id`,`happened`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (hour(happened))
SUBPARTITION BY HASH (id)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.01 sec)

mysql&gt;


I went to the OS and ran the following:

C:\&gt;cd \MySQL_5.6.15\data\test

C:\MySQL_5.6.15\data\test&gt;dir nums_comp*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.15\data\test

08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp1.ibd
08/31/2014  04:09 PM             8,594 nums_composite.frm
08/31/2014  04:09 PM                96 nums_composite.par
               8 File(s)        598,514 bytes
               0 Dir(s)  686,691,409,920 bytes free

C:\MySQL_5.6.15\data\test&gt;


Reason you cannot see the files ?

I can see my data files because I have innodb_file_per_table enabled by default in MySQL 5.6.

mysql&gt; show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql&gt;


In your case, you must have innodb_file_per_table disabled. The actual table was inside the system tablespace file ibdata1 (usually located in /var/lib/mysql)

My Guess


You are using a version before MySQL 5.6
innodb_file_per_table is set to 0 because of one of the following:

You are running on defaults (no my.cnf)
You have my.cnf with innodb_file_per_table=0



How to fix this:

STEP #1 : Enable innodb_file_per_table

Add this to the my.cnf

innodb_file_per_table=1


STEP 2 : Restart mysql

service mysql restart


STEP 3 : Regenerate the table

CREATE TABLE nums_composite_new LIKE nums_composite;
INSERT INTO nums_composite_new SELECT * FROM  nums_composite;
ALTER TABLE nums_composite RENAME nums_composite_old;
ALTER TABLE nums_composite_new RENAME nums_composite;
DROP TABLE nums_composite_old;


The .ibd files should now be visible

GIVE IT A TRY !!!

UPDATE 2014-08-31 16:37 EDT

You asked me


  I am in ubuntu.Does it change anything? 


The only thing that changes is where my.cnf should be. Run this

ls -l /etc/mysql/my.cnf


If the file exists, open it in an editor and add innodb_file_per_table=1 under the [mysql] header

If the file does not exist, run this

echo "[mysqld]" &gt; /etc/mysql/my.cnf
echo "innodb_file_per_table=1" &gt;&gt; /etc/mysql/my.cnf


Then, you can do STEP 2 and STEP 3