MySQL PHP | "SELECT FROM table" using "alphanumeric"-UUID. Speed vs. Indexed Integer / Indexed Char

Question

At the moment, I select rows from 'table01 and table02' using:

SELECT t1.*,t2.* FROM table01 AS t1 
INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID) 
WHERE t1.UUID = 'whatever';

The UUID column is a unique index, type: char(15), with alphanumeric input. 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. SELECT t1.*,t2.* FROM table01 AS t1 
   INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID) 
   WHERE t1.ID = $id;

Or keep it the way it is now, using the UUID.

2. SELECT t1.*,t2.* FROM table01 AS t1 
   INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID) 
   WHERE t1.UUID = 'whatever';

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.

Answer 1

The second solution have the best performance. You will need to look up the row by the UUID in both solutions, but in the first solution you first do it by UUID, and then do a faster lookup by primary key, but then you've already found the right row by UUID so it doesn't matter that the second lookup is faster because the second lookup is unnecessary altogether.

Answer 2

Why not just try it out? Create a new db with those tables. Write a quick php script to populate the tables with more records than you can imagine being stored (if you're expecting 100k rows, insert 10 million). Then experiment with different indexes and queries (remember, EXPLAIN is your friend)...

When you finally get something you think works, put the query into a script on a webserver and hit it with ab (Apache Bench). You can watch what happens as you increase the concurrency of the requests (1 at a time, 2 at a time, 10 at a time, etc).

All this shouldn't take too long (maybe a few hours at most), but it will give you a FAR better answer than anyone at SO could for your specific problem (as we don't know your DB server config, exact schema, memory limits, etc)...