MySQL PHP | "SELECT FROM table" using "alphanumeric"-UUID. Speed vs. Indexed Integer / Indexed Char
- by dropson
At the moment, I select rows from 'table01' using:
SELECT * FROM table01 WHERE UUID = 'whatever';
The UUID column is a unique index. I know this isn't the fastest way to select data from the database, but the UUID is the only row-identifier that is available to the front-end.
Since I have to select by UUID, and not ID, I need to know what of these two options I should go for, if say the table consists of 100'000 rows. What speed differences would I look at, and would the index for the UUID grow to large, and lag the DB?
Get the ID before doing the "big" select
1. $id = "SELECT ID FROM table01 WHERE UUID = '{alphanumeric character}'";
2. $rows = SELECT * FROM table01 WHERE ID = $id;
Or keep it the way it is now, using the UUID.
1. SELECT FROM table01 WHERE UUID '{alphanumeric character}';
Side note: All new rows are created by checking if the system generated uniqueid exists before trying to insert a new row. Keeping the column always unique.
The "example" table.
CREATE TABLE Table01 (
ID int NOT NULL PRIMARY KEY,
UUID char(15),
name varchar(100),
url varchar(255),
`date` datetime
) ENGINE = InnoDB;
CREATE UNIQUE INDEX UUID
ON Table01
(UUID);
