MySQL AUTO INCREMENT vs. ID generated from PHP

Question

MySQL AUTO INCREMENT vs. ID generated from PHP

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I have tables which will have millions of records in them. What is the best way to create the unique ID of these tables?

According to MySQL, there is the use of AUTO_INCREMENT, but at a certain number of registers it slows down each time.

In the case of ID generation from PHP I have the following code:

private function crearIdentificacion(){
    $min = 1;
    $max = 0;
    switch(PHP_INT_SIZE) {
        case 4:
            $max = 2147483648;
            break;
        case 8:
            $max = 9223372036854775807;
            break;
        default:
            break;
    }
    $diferencia   = bcadd(bcsub($min,$max),1);
        $rand_percent = bcdiv(mt_rand(), mt_getrandmax(), 8);
        return abs(bcadd($min, bcmul($diferencia, $rand_percent, 8), 0));
}

Which creates an ID and its size depends on whether it is 32bit or 64bit, but I am in doubt. Because I know that when a table contains too much information access to it is getting slower.

mysql php

asked by Máxima Alekz 04.04.2017 в 02:10

source

2 answers

1

You can create the key yourself, this example that I leave with you, does not repeat itself. You can give the length you want. The example is only limited to 9999 daily but you can do it as you wish.

So that you do not become slow at the time of the search, you only have to place the index in the field of the mysql table.

     $resultset = $this->db->query("SELECT Max(campo_llave) AS llave FROM nombre_tabla");
     $row = mysqli_fetch_array($resultset);
     $datecodetable = substr($row['llave'], 0,6);
     $codem = substr($row['llave'], -4);
     if(date("ymd") > $datecodetable){
         $codem = "0000";
     }
     $codem++;

     switch(strlen($codem)){
         case 1: $codem = "000$codem"; break;
         case 2: $codem = "00$codem"; break;
         case 3: $codem = "0$codem"; break;
         case 4: $codem = "$codem"; break;
     }
     $code = "".(date('ymd'))."$codem";

answered by 04.04.2017 в 18:41

How to limit the number of characters in a JTextField in Java? [closed] How to obtain the model of a UITableViewCell

score 3 · Accepted Answer

3

You could also generate a GUID with PHP

$id = com_create_guid();

Or insert the GUID directly into MySQL

insert tabla (id, ...) values (UUID(), ...)

answered by 04.04.2017 / 02:20

source