История изменений

Исправление sparks, 13.06.23 14:37 (текущая версия) :

Из интереса, сделал нечто подобное твоей дб локально и заполнил её рандомными данными, скриптом, всё выполнение занимает несколько минут

import mysql.connector
import random
import string
import uuid
import time

def generate_string(length):
	alphabet = string.ascii_lowercase + string.ascii_uppercase
	return ''.join(random.choice(alphabet) for i in range(length))

def generate_table1_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	add_record_query = "insert into tblMain (name, anothername, testc, uuid, sha2hash) values (%s, %s, %s, %s, sha2(name, 256))"
	
	for i in range(amount):
		new_record = (generate_string(40), generate_string(50), generate_string(10), str(uuid.uuid4()))
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()

def generate_table2_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	cursor.execute("select id from tblMain order by rand() limit {size}".format(size=amount))

	id_set = [record[0] for record in cursor.fetchall()]

	add_record_query = "insert into tblSubtable1 (id, subname) values (%s, %s)"

	for i in range(amount):
		new_record = (random.choice(id_set), generate_string(40))
		print(new_record)
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()	

def generate_table3_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	cursor.execute("select id from tblMain order by rand() limit {size}".format(size=amount))

	id_set = [record[0] for record in cursor.fetchall()]

	add_record_query = "insert into tblSubuuid (id, subname) values (%s, %s)"

	for i in range(amount):
		new_record = (random.choice(id_set), str(uuid.uuid4()))
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()		

try:
	mysql_client = mysql.connector.connect(user='root', password='root', host='localhost', database='perf_test')

	start = time.time()
	print("Filling main table...")
	generate_table1_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	start = time.time()
	print("Filling second table...")
	generate_table2_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	start = time.time()
	print("Filling third table...")
	generate_table3_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	mysql_client.close()
except mysql.connector.Error as e:
	print(e)
finally:
	print("We've done here")

Исходная версия sparks, 13.06.23 14:35:

import mysql.connector
import random
import string
import uuid
import time

def generate_string(length):
	alphabet = string.ascii_lowercase + string.ascii_uppercase
	return ''.join(random.choice(alphabet) for i in range(length))

def generate_table1_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	add_record_query = "insert into tblMain (name, anothername, testc, uuid, sha2hash) values (%s, %s, %s, %s, sha2(name, 256))"
	
	for i in range(amount):
		new_record = (generate_string(40), generate_string(50), generate_string(10), str(uuid.uuid4()))
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()

def generate_table2_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	cursor.execute("select id from tblMain order by rand() limit {size}".format(size=amount))

	id_set = [record[0] for record in cursor.fetchall()]

	add_record_query = "insert into tblSubtable1 (id, subname) values (%s, %s)"

	for i in range(amount):
		new_record = (random.choice(id_set), generate_string(40))
		print(new_record)
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()	

def generate_table3_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	cursor.execute("select id from tblMain order by rand() limit {size}".format(size=amount))

	id_set = [record[0] for record in cursor.fetchall()]

	add_record_query = "insert into tblSubuuid (id, subname) values (%s, %s)"

	for i in range(amount):
		new_record = (random.choice(id_set), str(uuid.uuid4()))
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()		

try:
	mysql_client = mysql.connector.connect(user='root', password='root', host='localhost', database='perf_test')

	start = time.time()
	print("Filling main table...")
	generate_table1_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	start = time.time()
	print("Filling second table...")
	generate_table1_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	start = time.time()
	print("Filling third table...")
	generate_table1_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	mysql_client.close()
except mysql.connector.Error as e:
	print(e)
finally:
	print("We've done here")