What is the fastest way to generate 1 billion custom unique code using python and mariadb?

Question

This is the code :

import mysql.connector as mariadb
import time 
import random

mariadb_connection = mariadb.connect(user='root', password='xxx', database='UniqueCode', 
port='3306', host='192.168.xx.xx')

cursor = mariadb_connection.cursor()

FullChar = 'CFLMNPRTVWXYK123456789' # i just need that char
total = 5000
count = 10
limmit = 0
count = int(count)
entries = []
uq_id = 0
total_all = 0

def inputDatabase(data):
    try:
       maria_insert_query = "INSERT INTO SN_UNIQUE_CODE(unique_code) VALUES (%s)"
       cursor.executemany(maria_insert_query, data)
       mariadb_connection.commit()
       print("Commiting " + str(total) +  " entries..")
    except Exception:
       maria_alter_query = "ALTER TABLE UniqueCode.SN_UNIQUE_CODE AUTO_INCREMENT=0"
       cursor.execute(maria_alter_query)
       print("UniqueCode Increment Altered")
while (0 < 1) :
   for i in range(total):
      unique_code = ''.join(random.sample(FullChar, count)) 
    
      entry = (unique_code)
      entries.append(entry)


   inputDatabase(entries)
   #print(entries)
   entries.clear()
   time.sleep(0.1)

Output:

Id unique_code
1 N5LXK2V7CT
2 7C4W3Y8219
3 XR9M6V31K7

The code above runs well, the time it takes to generate it is also fast, the problem I faced was when the unique_code stored in the tuple was to be entered into mariadb, to avoid data redundancy, i added a unique index in the unique_code column. The more data that is entered, the more checking of the unique code that will be entered, which makes the process of entering data into the database longer.

From that problem, how can I generate 1 billion data to the database in a short time?

note: the process will slow down when the unique_code that enters the database is > 150 million unique_codes

Thank's a lot

Answer 1

The quick way

If you want to insert many records into the database, you can bulk-insert them as you do now.

I would recommend you disable the keys on the table before inserting and skip the check for unique. Else you will have a bad time like @CryptoFool mentioned.

ALTER TABLE SN_UNIQUE_CODE DISABLE KEYS;

<run code>

ALTER TABLE SN_UNIQUE_CODE ENABLE KEYS;

If I were you, then I would try to play around with the maximum you can insert at once. Try changing max_allowed_packet variable in MariaDB if necessary.

The table

It seems like your unique_code could be a natural key. Therefore you could remove the auto_incremented variable, it won't bring much performance but it is a start.