Let's compare them

PARTITION SIZE

If you have the following:

• 100 million rows in a table
• BTREE indexing
• Each Page in the BTREE holds 1024 keys

What would the metrics look like?

Since LOG(100000000)/LOG(2) = 26.575424759099, a BTREE index with 1024 keys per page treenode would have a tree height of only 3 (CEILING(LOG(100000000)/LOG(1024))). With only three pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 30 keys.

NUMBER OF PARTITIONS

If you have the following:

• 100 million rows in a table
• BTREE indexing
• Each Page in the BTREE holds 1024 keys
• You create 1024 parititions

The numbers would be slightly different.

Each partition should have about 97656 rows. What would the metrics become now?

Since LOG(97656)/LOG(2) = 16.575421065795, a BTREE index with 1024 keys per page treenode would have a tree height of only 2 (CEILING(LOG(97656)/LOG(1024))). With only two pages nodes, a binary search for the needed key in each accessed treenode would result in a pruning and isolating of about 20 keys.

CONCLUSION

Spreading out the keys just removes one tree level but essentially creates 1024 indexes. The queries won't know the difference. The search time would probably be nominal at best in favor of partitions. However, make sure all the data is active. Otheriwse, you may be hitting just a few partitions, while other partitions with rarely-accessed data just takes up space and are never accessed frequently enough to justify the partitioning. You may have different performance metrics to worry about that are more blatent (such as internal defragmentation in XFS, ext3 vs ext4, etc.) You also need to worry about which storage engine you are using because:

• InnoDB indexing would be a little messier in comparison to MyISAM because of having to manage a clustered index
• InnoDB does double writing of data in ibdata1 as well as the current log file (ib_logfile0 or ib_logfile1)

There is a CREATE TABLE syntax where you can declare the data folder and index folder under the partition_definition of a partition_option:

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir'] <<<<<---------
        [INDEX DIRECTORY [=] 'index_dir'] <<<<<---------
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

CAVEATS

• I know it works for MyISAM because I wrote about it in September 2011 : Dynamically changeable database. For an already existing MyISAM table, it can done from the OS by moving the .MYD and .MYI to another disk and setting up two symlinks. I wrote about that in May 2012 : Can I have one table be an "alias", or "symlink", to another?. When done correctly, SHOW CREATE TABLE will display DATA DIRECTORY and INDEX DIRECTORY and their respective folders.

• I personally implemented this in production for a client when I did the following

  • Converted mysql.general_log from CSV to MyISAM
  • Moved /var/lib/mysql/mysql/general_log.MYD to a large 3TB disk
  • Moved /var/lib/mysql/mysql/general_log.MYI to a large 3TB disk
  • Setup /var/lib/mysql/mysql/general_log.MYD as a symlink to the real .MYD
  • Setup /var/lib/mysql/mysql/general_log.MYI as a symlink to the real .MYI
  • enabled the general log
  • Watched the client's general log growth to 1.1TB
  • I wrote up how to set this up back in June 2011 : Mail alert from MySQL when databases are edited (under Option 3 : You Could Monitor the general log)

• Using hard links, it can be done to MyISAM tables in Windows, although the effort to do so may not be worth it. I wrote about that back in January 2012 : Can I specify a different disk for a specific MySQL table?

• I do not believe [DATA DIRECTORY [=] 'data_dir'] and [INDEX DIRECTORY [=] 'index_dir'] were implemented for InnoDB. Even if the it were implemented for InnoDB, I would adamently advised against it and I wrote about why you should not back in August 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?

Your Original Question

MySQL 5.5 Only : With regard to your question in the heading, Where are partitions defined in mysql, you can run this query to locate all tables that have partitions:

select count(1) partition_count,table_schema,table_name
from information_schema.partitions
group by table_schema,table_name
having count(1) > 1;

Here is a sample:

mysql> select count(1) partition_count,table_schema,table_name
    -> from information_schema.partitions
    -> group by table_schema,table_name
    -> having count(1) > 1;
+-----------------+--------------+-------------------------+
| partition_count | table_schema | table_name              |
+-----------------+--------------+-------------------------+
|              31 | clickit      | t_keyword_conversion_ga |
|               2 | part         | myoldpart               |
|               3 | test         | messages                |
|             256 | test         | ngram_key               |
|               2 | test         | rc1                     |
+-----------------+--------------+-------------------------+
5 rows in set (1.60 sec)

mysql>

You can get the complete definition by running SHOW CREATE TABLE db.tbl\G from any table from the result of that query.