Which of these two DB designs will lead to faster queries?

Question

Which will be faster, what will the answer depend on:

1) Looking up a numerical id in a DB table A, and then querying another table B for all instances of that numerical ID. where table B is likely to be 100x larger.

2) Querying table B directly using for the string in question.

Now that I write this I think intuitively solution 1 is faster (which is what I was going to do anyway) but would like to know your thoughts.

My reasoning is that it must be faster to compare two numbers wrt to comparing two strings, because, on average the numbers are shorter.

Answer 1

In effect you are building your own index. Databases do that really well. Add appropriate indexes to table B and let the Db do the work.

Answer 2

It will mostly depend on the SQL engine you use (Oracle, SQL Server, MySQL, Postgres, etc) as they'll all optimise in different ways.

From my experience with SQL Server, I've not seen any measurable difference between searching for a number or searching for a string, providing the target columns are suitably indexed. Thus I'd probably opt for (2) as it's one less SQL query. However I'd be benchmarking each solution as well to verify the timings...

I've had too many occasions where a decsion was made "for performance reasons" backfire or have negligable benefit, that I'd base any decision like this on hard numbers rather than a theoretical basis.

Answer 3

Is this more a question on normalisation?

Is the string stored multiple times in your table B? Are you asking if you should store this string in table B as well as table A.

I would say option 1 is the best way to go.

TableA - ID_A, Name, Data...

TableB - ID_B, ID_A, Data...

SELECT
    *
FROM
    TableA
INNER JOIN
    TableB
ON
    TableA.ID_A = TableB.ID_A
WHERE
    TableA.Name LIKE '%search%'