Hello, nathanelrick!

Thursday, March 22, 2012, 11:15:06 AM, you wrote:

>> Memory is cheap, just buy it.

n> i was speaking of more than 128GB of memory :)

well, great.

>> About splitting table I have an opinion:
>> if some rows are being accessed more than other, than split table.

oops, meant colums, not rows.

>> This will
>> - make row size less for most reading rows
>> - make less pages for most reading data

n> you mean if some query need often for exemple fieldA, fieldB but
n> rarely we ask for fieldz and feldw then it's better to split the table and in this way
n> the select fieldA, fieldB from table1 will be more faster than the
n> select fieldA, fieldB from AllFieldtable ...

yes, exactly.

n> (and that was my suprise to discover it (see my past thread) as i
n> was thinking than when we access a row via index, the row length is not very matter)

server reads database by pages, not by "rows" or "columns". The more
fields in a row, the bigger is row, and the more data pages needed to
store same number of rows.

There is no difference accessing rows by index or some other way.
1. If you have 10 rows per page, there will be 100 pages for 1000 rows.
2. If you will have 20 rows per page, there will be 50 pages for 1000
rows.

So, to read 1000 rows server will read 100 pages for the first case,
and 50 for the second. It's a simple math. Reading less pages is
faster.

n> hmmm need to try ... if the speed we win in some query will be not
n> lost in the join we need to do in other ... also i discover than using pooling of
n> prepared query can speed (a lot) so yes need to try... but my
n> first opinion is to join the table

I was speaking mostly about joins. Same issue can be found for blobs
or varchars, that for example, store "secondary" information that is
not needed for most of the queries.
For example, it's a picture of a user in the blob, or, resume of
a user in a varchar field, etc.
Such type of information is never "grouped" or computed, and always
be viewed for particular "user record" only.
Thus, it can be stored in additional table, related by 1-1.

--
Dmitry Kuzmenko, www.ib-aid.com