Why mysql partitioning has no effect in my case

Question

I try to test benefits of partitioning in Mysql

I created two tables: one partitioned other not.

Every table has 10M of records in it.

I want fast querying by "user_to_id" column.

Partitioned table (1024 parts):

CREATE TABLE `neworder10M_part_byuser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_from_id` int(11) NOT NULL,
  `site_to_id` int(11) NOT NULL,
  `user_from_id` int(11) NOT NULL,
  `user_to_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`,`user_to_id`),
  KEY `composite_cover` (`user_to_id`,`user_from_id`,`site_from_id`,`site_to_id`,`created`)
) ENGINE=InnoDB 
/*!50100 PARTITION BY HASH (user_to_id)
PARTITIONS 1024 */ |

Table with clustered key (not partitioned):

CREATE TABLE `neworder_10M` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_from_id` int(11) NOT NULL,
  `site_to_id` int(11) NOT NULL,
  `user_from_id` int(11) NOT NULL,
  `user_to_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`user_to_id`,`id`),
  UNIQUE KEY `id_UQ` (`id`)
) ENGINE=InnoDB;

when i benchmark both tables with python script for 1000 reqs:

for i in xrange(1,REQS):
    user_id = random.randint(1,10000);
    cursor.execute("select * from neworder10M_part_byuser where user_to_id=%s;" % (user_id))

Partitioned table: 22 rps Not partitioned: 22.7 rps

Why are there no speed benefits with the partitioned table? As I expect smaller data - faster querying.

And explain also shows that partition used:

mysql> explain select * from neworder10M_part_byuser where user_to_id=6867;
+----+-------------+-------------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table                   | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | neworder10M_part_byuser | p723       | ref  | composite_cover | composite_cover | 4       | const | 1009 |   100.00 | Using index |
+----+-------------+-------------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+

but I did not see real speed improve in reality.... what I am doing wrong ?

Tables fill code:

def send_orders(cur,users=10000,orders=10000000):
    for i in xrange(1,orders+1): //10000000 rows here
        print i
        from_user = random.randint(1,users)
        to_user = random.randint(1,users)
        from_site = random.randint(1,10000)
        to_site = random.randint(1,10000)
        cur.execute("INSERT INTO neworder (site_from_id, site_to_id,user_from_id, user_to_id,created)  VALUES ('%d','%d','%d','%d',NOW());" % (from_user,to_user,from_site,to_site))

version of mysql: Ver 14.14 Distrib 5.7.12, for Linux (x86_64). Hard drive is ssd.

Answer 1

We wouldn't expect there to be much difference in performance for the SELECT statements, since the queries are making use of an index range scan, and because the query of the partitioned table is pruning partitions.

Without the partition pruning, we'd expect slower performance with a SELECT against the partitioned table. Since that would be 1024 indexes that need to be checked vs. just one index.

The idea that partitioning improves query performance is a fallacy.