cristi _b
cristi _b

Reputation: 1813

Mysql Optimization

I have 2 tables in MySql

Table_A { pk_A VARCHAR 150 PRIMARY KEY, random_data } ~ 9500 rows
Table_B { pk_B VARCHAR 150, more_random_data } ~ 50000 rows

Relationship between them is one-to-many, pk_A will be found multiple times in Table_B on col pk_B.

My problem is:

How can i improve my database performance (i could modify my pk's from varchar to int(11)) but i wondered if there are other workarounds available

Thanks

Upvotes: 0

Views: 124

Answers (1)

SingleNegationElimination
SingleNegationElimination

Reputation: 156148

A few things to speed up your queries. As the many comments suggest; you always want to keep your primary keys as small as is reasonable. A short string, say VARCHAR(10) won't cause you much pain, but much larger than that, you may want to use a surrogate key. This can be made more or less transparent by making the natural key be unique, but not in the primary key.

Table_A { pk_A VARCHAR 150 PRIMARY KEY, random_data } ~ 9500 rows Table_B { pk_B VARCHAR 150, more_random_data } ~ 50000 rows

Your schema should probably look a bit like this:

CREATE TABLE `Table_A` (
    `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
    `A_name` VARCHAR(150) UNIQUE NOT NULL,
    -- other columns
) ENGINE = InnoDB;

CREATE TABLE `Table_B` (
    `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
    `B_name` VARCHAR(150) UNIQUE NOT NULL,
    `A_id` INTEGER REFERENCES `Table_A`(`id`),
    -- other columns
) ENGINE = InnoDB;

Upvotes: 2

Related Questions