Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:
Right. And wrong. Internal storage format does not matter here.
Results for nonprepared statements are sent and stored in
character representation[1]

That means a row (INT, DOUBLE, CHAR(5)) with values
(123456, 3.141592654, "fubar") will use 6+11+5 bytes just to
represent the data. Plus some overhead (basically a length
coded number for each field).

275 Mio rows will be around 275Mio * 10 bytes netto, probably
more like 275 Mio * 20 bytes brutto. That's a lot.

Things are different for prepared statements. Then results are
sent using a binary representation.

[1] the details are here:
http://forge.mysql.com/wiki/MySQL_In..._Data_Pac ket


XL

Axel Schwenke wrote:
You're actually transferring numeric data in character format? That
would be extremely stupid.

So I guess then you have to convert it back into non-character values to
get the data in a C program.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:

Well, not me. But that's how Monty designed it ages ago.
How can you be a "MySQL Expert" without knowing that?

It avoids some problems with i.e. floating point rounding.
Also most data in DBMS can be considered characters anyway.

But yes, this design decision is questionable.

Either that or use the prepared statement API. Not that it
solves all problems like byte order or floating point format.
But at least you avoid the atoi(itoa(...)) madness.


XL

Axel Schwenke wrote:
Because I don't worry about thinks I can't control - like how MySQL
handles internal data transfers.

Not at all. In fact, in most major databases, the data is anything BUT
characters. Numeric and bit data is much more common. But then MySQL
isn't being used for major databases, is it?

Note that I'm not talking about "big" databases. I'm talking about
databases where data integrity and database performance are critical.
All of those use Oracle, DB/2 or SQL Server.

Just another reason why MySQL can't compete with real databases.

Still a stupid decision.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:
In databases I have seen, the vast majority of data (in terms of
storage size) is in non-numerical fields. I.e. INT vs. VARCHAR(100)
is already 1:25. Of course this varies with the problem domain.

Yeah. "All"

That must be the reason why MySQL AB^W^WSun Microsystems^W^WOracle
has "no" users who use MySQL for such databases.

Disclaimer: this was ironic

Looks like you completely ignore the fact that MySQL offers an
alternative - the prepared statement API, introduced in MySQL 4.1 -
that transferes parameters and results in binary.

And certainly your ideas about "real" databases and competition
are not as widespread as you want to make us believe. If you want
to advocate for your favorite "real" database, I suggest you send
such posts to a *.advocacy group.

EOD for me


XL

On Mon, 28 Jun 2010 07:30:17 -0400, Jerry Stuckle wrote:
Well, it has the edge that it's very very portable... Not even endian
issues to fret with. And in the vast majority of databases I've seen,
the amount of actual numeric data is pretty small compared to the stuff
that's text or may as well be. (Don't get me started about phone numbers
stored as type DOUBLE...)

*grin* C's probably faster than the network is.

--
Time is a great teacher, but unfortunately it kills all its pupils.
-- Hector Berlioz

"Peter H. Coffin" <hellsop (AT) ninehells (DOT) com> wrote:

-v

PLEASE!!!1!


SCNR, XL

On Mon, 28 Jun 2010 16:51:12 +0200, Axel Schwenke wrote:
Gah. A while back, I ended up spending about a month off and on undoing
that for a database of just shy of a million business and individual
contact records in a home-grown CRM system. Many Years Ago, the bright
spark that built the system picked that because it was more "efficient"
in terms of storage; I ran into a lot of that kind of thing working
with that client. The North American chunk wasn't too bad becuase the
phone numbers here are at least all the same number of digits, but in
the intervening time between Bright Spark had set aside the hash pipe
and "designed" this system, and when I got involved, the company had
expanded its presence in the UK and eastern Europe rather extensively,
and not all the phone numbers even started with the country codes
necessary for dialing them. That part I couldn't lay on Bright Spark,
but rather a couple of rather casual sales reps. I suppose I should
have been grateful in their cases that there wasn't a datatype of
COCKTAIL_NAPKIN... I spent a lot of time researching phone number
formats for different countries, wrote lots of little queries that
reformated the numbers as string, added the appropriate decorations to
the bare numeric characters, flagged the ones that didn't seem to be
right for what country/province/city the address(es) had associated with
it if I knew that they were funny (the NANPA data tables came in hugely
handy for that effort), and acquired a taste for single malts because I
couldn't drink enough beer to make me forget about it in the evenings.

--
Is it just me, or is it a clear indication that a thread is ending its
useful life is when people start debating the merits of the analogies
that have been posed rather than the original subject matter of the
thread? --Rhetorical question by Rainer Atkins founding "Atkins' Law"

Hi,

select value from bigatble

leads to problems.

In a professional environment you have xx GB data and 4 times this
amount index.

So you have always to: select value from bigtable where key=index

With correct indexes you get answer times < 1 sec even with > 100m
tables on computers with < 4 GB RAM.

I have done this many times just for fun with Oracle and MySQL.

If you don't have an index you didin't make your homework.

BTW: In Oracle you often have to use optimizer hint /*+ first_rows */ to
make Oracle use you correct index.

In my opinion MySQL with innobase is equal to Oracle, or DB2 or
something else even with databases > 100 GB,

MySQL with innobase -is- designed for LARGE databases.

kind regards,

Toni

PS: Play with MySQLWorkbench, for example it handles limits per default
PPS: Main problem of OP was buffering of the client process which was
discussed by others here.