1 query or 2 queries

[QUOTE=dotJoon;4760250]

[b]data in myTable[/b]

[COLOR="Blue"](id) name  city     country  food   height  weight  sex   class earning ... [/COLOR]

(1)   Tom  Toronto   Canada   pizza   180    90     male     5    40,000 ...
(2)   Mary Paris     France   bread   165    60     female   5    35,000 ...
(3)   Jack  London   England  fish    185    80     male     3    38,000 ...
:                                  :                                   :


Let’s suppose I have data in myTable like the above.

The number of the columns is more than 50 in myTable.
Some of the columns have long text data.
The number of the records is more than 10,000,000.

When I call data from myTable, I get a clue “name.”

So I use the code below.

select id, city, country, ...
from myTable
where [COLOR="Blue"]name[/COLOR] = "Mary"

But some names are not in any of the records in the code below.

select id, city, country, ...
from myTable
where name = "[COLOR="Red"]jemalla[/COLOR]"

Actually 90% of the calls can’t get data because there is no records for the name.

Only 10% of the DB calls can get data because there is the record for the name.

In this case I have two options for getting the data of the records.

[b]option1[/b]

select id, city, country, ...
from myTable
where name = "Mary"

option1 the above has 1 query, and option2 the below has 2 queries.
Which option has better performance?

(I guess that option1 would be better, but I need your confirmation.

But in case of option2, only when the 1st db call has the response, the 2nd calls will be run.
If there is no response for the 1st db call, the 2nd SQL calls will not be performed.

name column is already indexed.)

[b]option2[/b]

select [COLOR="red"]id[/COLOR]
from myTable
where name = "Mary"

select city, country, ...
from myTable
where id = $row([COLOR="Red"]id[/COLOR])

no, not really

what you need is to learn how to test two different options and compare the results yourself

:slight_smile:

I see no point in using your second option. It has an extra step that gains you nothing.