0

I have a partitioned mysql table, table size is about 100M rows:

CREATE TABLE `nearby` (
`id` int(10) unsigned NOT NULL,
`nearby_1` varchar(750) NOT NULL DEFAULT '',
`nearby_2` varchar(750) NOT NULL DEFAULT '',
`nearby_3` varchar(750) NOT NULL DEFAULT '',
`nearby_4` varchar(750) NOT NULL DEFAULT '',
`nearby_5` varchar(750) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (id)
PARTITIONS 30 */;

And I'm using following query to update this table (in multiprocess):

update nearby set nearby_5 =
"532487838^620340056^569758570^598224990^490445906^485712257^560741695^584147604^504840723^574686588^499373888^514048867^574839522^555681829^528849288^482987618^621473564^527953408^509892347^575024990^533233841^528365873^661634374^508770161^499694389^514308586^"
where id = 150986046

From what I understand this query should be fast because it's updating by primary key. But the explain in mysql tells me it's "using temporary":

+----+-------------+--------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table  | type  | possible_keys |   key   | key_len |  ref  | rows |            Extra             |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+------------------------------+
|  1 | SIMPLE      | nearby | range | PRIMARY       | PRIMARY |       4 | const |    1 | Using where; Using temporary |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+------------------------------+
13
  • I couldn't see nearby_forsale column in the table definition!! (I'll assume you meant nearby_5) Are you using 5.7 to run explain against UPDATE? The explain plan you shared says the "range" while, I guess I'd expect "const" instead!! Can you share what explain query you ran?
    – mysql_user
    Commented Nov 21, 2015 at 18:20
  • Sorry about the column name. I changed the name in the schema. But I didn't touch the explain query. Here's a screenshot of the explain grab.by/MbUe
    – Suanmeiguo
    Commented Nov 21, 2015 at 18:48
  • This is MySQL 5.6.
    – Suanmeiguo
    Commented Nov 21, 2015 at 18:48
  • 1
    Question: why are you partitioning the PK by hash? Commented Nov 21, 2015 at 19:55
  • 1
    @Suanmeiguo: I would not partition by hash on a PK, especially for innodb tables which uses clustered index. It also depends how many rows you have in your table. Clustered index are very fast when accessed by key whether you select, update or delete. The goal of partitioning is to group same data together whether it's by month or days, or by zip codes ; where partition by list would probably be appropriate. Commented Nov 23, 2015 at 1:27

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.