Mysql two ways to select where. Which way uses less resources and is faster?

Question

For example have url like domain.com/transport/cars

Based on the url want to select from mysql and show list of ads for cars

Want to choose fastest method (method that takes less time to show results and will use less resources).

Comparing 2 ways

First way

Mysql table transport with rows like

FirstLevSubcat | Text
---------------------------------
  1            | Text1 car
  2            | Text1xx lorry
  1            | Text another car

FirstLevSubcat Type is int

Then another mysql table subcategories

Id | NameOfSubcat
---------------------------------
1  | cars
2  | lorries
3  | dogs
4  | flats

Query like

 SELECT Text, AndSoOn FROM transport 
 WHERE 
 FirstLevSubcat = (SELECT Id FROM subcategories WHERE NameOfSubcat = `cars`)

Or instead of SELECT Id FROM subcategories get Id from xml file or from php array

Second way

Mysql table transport with rows like

FirstLevSubcat | Text
---------------------------------
  cars         | Text1 car
  lorries      | Text1xx lorry
  cars         | Text another car

FirstLevSubcat Type is varchar or char

And query simply

 SELECT Text, AndSoOn FROM transport 
 WHERE FirstLevSubcat = `cars`

Please advice which way would use less resources and takes less time to show results. I read that better select where int than where varchar SQL SELECT speed int vs varchar So as understand the First way would be better?

Answer 1

Yup. As the SO link in your question suggests, int comparison is faster than character comparison and yield faster fetch. Keeping this in mind, first design would be considered as better design. However sub queries are never recommended. Use join instead.

eg:

 SELECT t.Text, t.AndSoOn FROM transport t
 INNER JOIN subcategories s ON s.ID = t.FirstLevSubcat
 WHERE s.NameOfSubcat = 'cars'

Answer 2

The first design is much better, because you separate two facts in your data:

1. There is a category 'cars'.
2. 'Text1 car' is in the Category 'cars'.

Imagine, in your second design you enter another car, but type in 'cors' instead of 'cars'. The dbms doesn't see this, and so you have created another category with a single entry. (Well, in MySQL you could use an enum column instead to circumvent this issue, but this is not available in most other dbms. And anyhow, whenever you want to rename your category, say from 'cars' to 'vans', then you would have to change all existing records plus alter the table, instead of simply renaming the entry once in the subcategories table.)

So stay away from your second design.

As to Praveen Prasannan's comment on sub queries and joins: That is nonsense. Your query is straight forward and good. You want to select from transport where the category is the desired one. Perfect. There are two groups of persons who would prefer a join here:

1. Beginners who simply don't know better and always join from the start and try to sort things out in the end.
2. Experienced programmers who know that some dbms often handle joins better than sub-queries. But this is a pessimistic habit. Better write your queries such that they are easy to read and maintain, as you are already doing, and only change this in case grave performance issues occur.