A am currently doing a time analysis on SQL queries using MySQL, where each query uses one type of operation and for each query I test with B Tree and Hash indices. The following code is responsible for creating the indices and calling a function that do the query a number of times.

def instanceProcessing(queryInstance):

    # queryInstance[0] is a queryId
    print(queryInstance[0] + "n")

    # Add the analyze keyword
    # queryInstance[1] is the SQL
    querySQL = "EXPLAIN ANALYZE " + queryInstance[1]

    # Test without indices
    print("  No index")
    executionLoop(queryInstance[0], querySQL, "ni", "")

    # Tests with B Tree
    # queryInstance[2] is a list with the indices (relation.atribute)
    for testIndices in queryInstance[2]:

        print("  B+ tree - " + testIndices)
        relationName, attributeName = testIndices.split(".")
        indexOn = relationName + " (" + attributeName + ")"
        indexName = queryInstance[0] + "Btree" + relationName + atributeName

        # Create the index
        try:
            createIndexQuery = "CREATE INDEX " + indexName + " USING BTREE ON " + indexOn
            myCursor = mydb.cursor()
            myCursor.execute(createIndexQuery)
            myCursor.fetchall()

        except mysql.connector.Error as err:
            print("  *** Something went wrong creating B+ tree: {}".format(err) + "n")
            continue

        # Do the query
        executionQuery(queryInstance[0], querySQL, "btree", testIndices)

        # Delete index
        deleteIndexQuery = "DROP INDEX " + indexName + " ON " + tableName
        myCursor = mydb.cursor()
        myCursor.execute(deleteIndexQuery)
        myCursor.fetchall()

    # Tests with Hash
    for testIndices in queryInstance[2]:

        print("  Hash - " + testIndices)
        relationName, attributeName = testIndices.split(".")
        indexOn = relationName + " (" + attributeName + ")"
        indexName = queryInstance[0] + "Hash" + relationName + atributeName

        # Create index
        try:
            createIndexQuery = "CREATE INDEX " + indexName + " USING HASH ON " + indexOn
            myCursor = mydb.cursor()
            myCursor.execute(createIndexQuery)
            myCursor.fetchall()

        except mysql.connector.Error as err:
            print("  *** Something went wrong creating Hash index: {}".format(err) + "n")
            continue

        # Do the query
        queryLoop(queryInstance[0], querySQL, "hash", testIndices)

        # Delete index
        deleteIndexQuery = "DROP INDEX " + indexName + " ON " + tableName
        myCursor = mydb.cursor()
        myCursor.execute(deleteIndexQuery)
        myCursor.fetchall()

The problem I am having has to do with the creation of the indices. I tried to name them in a unique way, so I would not create to indices with the same name, even if I delete them right after using. The names are:

# B Tree
indexName = queryInstance[0] + "Btree" + relationName + attributeName

# Hash index
indexName = queryInstance[0] + "Hash" + relationName + attributeName

But sometimes when running, i get the error Something went wrong creating B+ tree: 1061 (42000): Duplicate key name '7bBtreePostsPostTypeId', but I don’t see how this is possible. In this example, there is only one query with id 7b, and the index on Posts(PostTypeId) with B Tree is tested only once. Is there something I am missing?

Sometimes this happens when I am creating index on a Key, sometimes on a foreign keys. It also happens to indexes over relations and attributes that worked before. I don’t see a pattern. Thanks in advance!

I even tried creating names using uuid to be unique and avoid repetition, but I still got the error. I was expecting that different index names would not be a problem.