MySQL is pretty slow after 50-100k rows whats happen? i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues.

i testing it of on a developer server ( 2 cores, 4gb ram ) but i still got issues problems, here is my stored procedure i have typed.

DROP PROCEDURE IF EXISTS insertDistributorProduct;

DELIMITER $$

CREATE PROCEDURE insertDistributorProduct(
    distributorUUID VARCHAR(36),
    distributorTitle VARCHAR(256),
    distributorDescription TEXT,
    distributorSKU VARCHAR(128),
    manufacturer VARCHAR(64),
    manufacturerSKU VARCHAR(128),
    productEan BIGINT(20),
    productCostPrice INT(11),
    productStock INT(11),
    productStockExpected DATE
)
BEGIN
    DECLARE _manufacturerUUID VARCHAR(36) DEFAULT NULL;
    DECLARE _distributorProductUUID VARCHAR(36) DEFAULT NULL;

    SET _manufacturerUUID = ( SELECT m.manufacturerUUID FROM manufacturer m
            WHERE LOWER(m.manufacturerTitle) = LOWER(manufacturer) LIMIT 1 );

    -- If manufacturer not exists insert it
    IF ( _manufacturerUUID IS NULL ) THEN

        SET _manufacturerUUID = ( SELECT ma.manufacturerUUID FROM manufacturerAlias ma
                WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer) LIMIT 1 );

        -- _manufacturerUUID not found in manufacturer and _manufacturerAlias so insert
        IF ( _manufacturerUUID IS NULL ) THEN

            SET _manufacturerUUID = UUID();

            INSERT INTO manufacturer(manufacturerUUID,manufacturerTitle,added)
            VALUES(_manufacturerUUID,manufacturer,NOW());

        END IF;

    END IF;

    SET _distributorProductUUID = ( SELECT dp.distributorProductUUID FROM distributorProduct dp
            WHERE dp.distributorUUID = distributorUUID
              AND LOWER(dp.distributorSKU) = LOWER(distributorSKU) );

    -- Update distributorProduct if its all ready found
    IF ( _distributorProductUUID IS NOT NULL ) THEN

        UPDATE 
            distributorProduct dp

        SET 
            dp.distributorTitle = distributorTitle,
            dp.distributorDescription = distributorDescription,
            dp.manufacturerUUID = _manufacturerUUID,
            dp.manufacturerSKU = manufacturerSKU,
            dp.productEan = productEan,
            dp.productCostPrice = productCostPrice,
            dp.productStock = productStock,
            dp.productStockExpected = productStockExpected,
            dp.updated = NOW(),
            dp.activated = 1

        WHERE
            dp.distributorProductUUID = _distributorProductUUID

        LIMIT
            1;

    -- Insert new product to distributorProduct if not exists.
    ELSE

        INSERT INTO distributorProduct (distributorProductUUID,distributorUUID,manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,added)
        VALUES(UUID(),distributorUUID,_manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,NOW());

    END IF;

END
$$


My problem is that every time I get over 50-100k rows, this script takes longer and longer time, and I have to try to look up my index but still nothing happens.

how can I debug and perfume my database more? every day i need to run this producer like 1-1,4milion times between 4-5 hours max, its update all our products but right now it's useless.

The first time 60k rows takes 15min, and after that 60k more ( total 120k ) this time its taken like 30-45min to run, and I still don't know why it happened.  What did I do wrong?

Everything I tested here is running in virtual box software, I don't have to test this on real hardware.
This is what happened:

UUID VARCHAR(36)


UUIDs (GUIDs, MD5s, etc) are very random.  That is, if you can't keep the entire table (or index) cached in RAM, the 'next' lookup is likely to cause a disk hit.  And disk hits are a big factor (think 90%) of the cost of performing queries.

More discussion here.  That includes a few partial remedies.

More importantly

WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer)


has terrible performance because it hides an indexable column inside a function (LOWER()).  Instead, make sure those two columns have a collation ending with _ci (as in "Case Insensitive").  Then simply do

WHERE ma.aliasTitle = manufacturer


and have

INDEX(aliasTitle)
INDEX(manufacturerTitle)
INDEX(distributorSKU)


If you run into some index limit size, please provide MySQL version and SHOW CREATE TABLE.  Meanwhile, distributorTitle VARCHAR(256) is likely to cause trouble.  Changing to (255) may work; (191) may be needed for 5.5 or 5.6; beware of truncating strings.

One more thing -- When you CREATE the procedure, be sure to have established the collation for the connection that is consistent with the columns.  For example, if you are using CHARACTER SET utf8 COLLATE utf8_general_ci for the columns, then you could do it this way:

SET NAMES utf8 COLLATE utf8_general_ci;
DELIMITER //
CREATE ...
//
DELIMITER ;