CODEWITHSUNDEEP
mysqlpartitioning
Federico Coco
Federico Coco

Reputation: 11

Select with join and partition table

I have 3 tables
Table bases
id int PK
descripcion char
estado int
tipo int

Table prospectos_x_bases
id int PK
base int
prospecto
provincia
estado int

Table prospectos (this table is partition by field provincia)
provincia int
id int
nombre char
telefono_fijo int
telefono_movil int
domicilio char
PK is provincia,id
SK telefono_fijo
SK telefono_movil

If I run a select with join like this

EXPLAIN format=json
 SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre
    FROM  prospectos_x_bases
    JOIN  prospects
       ON prospectos.provincia = prospectos_x_bases.provincia
      and  prospectos_x_bases.prospecto = prospectos.id;

The query searches all the partitions

but if i change to this 

EXPLAIN format=json 
SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre
    FROM  prospectos_x_bases
    JOIN  prospectos  ON prospectos.provincia = 20
      and  prospectos_x_bases.prospecto = prospectos.id;

The query woks ON one partition

I know this is ok because in the first query the engine does not know the value of provincia field in advance so it searches all the partitions

So my question is how to make this select so that the query uses one partition?

Can I do 2 selects instead of a join?

Do I have to save the results from the first query ,and then run the second query?

PARTITION BY RANGE (provincia)
(
PARTITION p01 VALUES LESS THAN (2) ENGINE=InnoDB,
PARTITION p02 VALUES LESS THAN (3) ENGINE=InnoDB,
PARTITION p03 VALUES LESS THAN (4) ENGINE=InnoDB,
PARTITION p04 VALUES LESS THAN (5) ENGINE=InnoDB,
PARTITION p05 VALUES LESS THAN (6) ENGINE=InnoDB,
PARTITION p06 VALUES LESS THAN (7) ENGINE=InnoDB,
PARTITION p07 VALUES LESS THAN (8) ENGINE=InnoDB,
PARTITION p08 VALUES LESS THAN (9) ENGINE=InnoDB,
PARTITION p09 VALUES LESS THAN (10) ENGINE=InnoDB,
PARTITION p10 VALUES LESS THAN (11) ENGINE=InnoDB,
PARTITION p11 VALUES LESS THAN (12) ENGINE=InnoDB,
PARTITION p12 VALUES LESS THAN (13) ENGINE=InnoDB,
PARTITION p13 VALUES LESS THAN (14) ENGINE=InnoDB,
PARTITION p14 VALUES LESS THAN (15) ENGINE=InnoDB,
PARTITION p15 VALUES LESS THAN (16) ENGINE=InnoDB,
PARTITION p16 VALUES LESS THAN (17) ENGINE=InnoDB,
PARTITION p17 VALUES LESS THAN (18) ENGINE=InnoDB,
PARTITION p18 VALUES LESS THAN (19) ENGINE=InnoDB,
PARTITION p19 VALUES LESS THAN (20) ENGINE=InnoDB,
PARTITION p20 VALUES LESS THAN (21) ENGINE=InnoDB,
PARTITION p21 VALUES LESS THAN (22) ENGINE=InnoDB,
PARTITION p22 VALUES LESS THAN (23) ENGINE=InnoDB,
PARTITION p23 VALUES LESS THAN (24) ENGINE=InnoDB,
PARTITION p24 VALUES LESS THAN (25) ENGINE=InnoDB,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

A real query 

SELECT id FROM bases
     WHERE bases.estado = 0;
WHILE($row = mysqli_fetch_array($result))
{         
  $base = $row['id'];
  SELECT  prospectos_x_bases.base, prospectos.id,prospectos.nombre,
          prospectos.telefono_fijo,prospectos.telefono_movil
    FROM  prospectos_x_bases
    JOIN  prospectos
       ON prospectos.provincia = prospectos_x_bases.provincia
     and  prospectos.id        = prospectos_x_bases.prospecto
    WHERE prospectos_x_bases.base = $base;
}

Upvotes: 0

Views: 465

Answers (1)

Rick James
Rick James

Reputation: 142483

 prospectos.provincia = 20

Makes it possible to decide which PARTITION to use.

Comparing to virtually anything other than "= constant" does not.

Usually a suitable INDEX is as good as partitioning for performance.

How big are the tables? Are those the complete queries?

More

SELECT  pb.base, p.id,p.nombre, p.telefono_fijo,p.telefono_movil
    FROM  prospectos_x_bases AS pb
    JOIN  prospectos AS p  ON p.provincia = pb.provincia
      and  p.id = pb.prospecto
    WHERE  pb.base = $base;

prospectos_x_bases would benefit from

INDEX(base, provincia,  prospect)

(I as assuming the other table continues to have PRIMARY KEY(provincia, id), whether or not it is Partitioned.)

The two queries could be combined, instead of the slower fetch a bunch of ids, then loop through the ids.

Getting rid of the Partitioning would eliminate your observation that "The query searches all the partitions". Instead, it will simply reach for the rows in one place instead of 25 places. In other words, it might be 25 times as fast!

Upvotes: 1

Related Questions