[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"
I am wandering in think that because there are many many records in myTable, database performance is not good in working with the code above.
Furthermore the table has many columns.
So I try to think that if I seperate the name column in myTable like the following, it could be better in performance.
[b]data in myTable[/b]
[COLOR="Blue"](id) city country food height weight sex class earning ... [/COLOR]
(1) Toronto Canada pizza 180 90 male 5 40,000 ...
(2) Paris France bread 165 60 female 5 35,000 ...
(3) London England fish 185 80 male 3 38,000 ...
: : :
[b]data in nameTable[/b]
[COLOR="Blue"](id) name[/COLOR]
(1) Tom
(2) Mary
(3) Jack
:
when I call some data about Jack, I’ll call the ID of him first with the code below.
select id
from nameTable
where [COLOR="Blue"]name[/COLOR] = "Jack"
Although nameTable has tens of millions of records, it has just 2 columns which have short data. So it will be not heavy and it will find ID fast.
and I can use the code below secondly.
select city, country ...
from myTable
where id=3
because id is already pointed, it will call the data fast.
Furthermore it’s numeric finding.
Sometimes, I do first call “Tom” which has no data in database
But when I do second call with “ID”, it the data in response with the “ID” are always exist in myTable.
What do you think in my wandering in think?
1 table or 2 tables?