MySQL vs XBASE

I am intrigued by the use of MySQL. I currently use the XBASE interface
included with Miva. My typical database has about 50,000 records and 2
independent indexes (some are 2 level indexes using field pairs). I have never
had a problem with performance, but still would like to get the most out of the
applications I write. What are the general advantages of using MySQL over
XBASE? And what are the performance advantages? (Any benchmark data available
between the two?)

Bob

MySQL vs XBASE



Bob-

They are two different worlds. Because you ask this I'm guessing you
have no experience using SQL, read up at:
<A HREF ="http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html">http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html</A>

The mysqlcommerce commerce library allows you to pass any SQL query to
MySQL and read the result:

<MvASSIGN name = "username"
value = "homer">
<MvASSIGN name = "password"
value = "flanders">
<MvASSIGN name = "database"
value = "mydatabase">

<MvCOMMENT> create a test database </MvCOMMENT>

<MvASSIGN name = "sql"
value = "create database &[database];">

<MvCOMMERCE metamethod = "mysqlcommerce"
fields = "username,password,sql">

<MvIF expr = "{ mysql_error }">

Error creating database!

<MvCOMMENT> ... error handling ... </MvCOMMENT>
<MvEXIT>

<MvELSE>

Database created successfully.


</MvIF>

</MvCOMMERCE>

<MvCOMMENT> create a test table </MvCOMMENT>

<MvASSIGN name = "sql"
value = "create table mytable (
id int(10) unsigned not null auto_increment,
name varchar(80),
primary key(id)
)">

<MvCOMMERCE metamethod = "mysqlcommerce"
fields = "username,password,database,sql">

<MvIF expr = "{ mysql_error }">

Error creating table!

<MvCOMMENT> ... error handling ... </MvCOMMENT>
<MvEXIT>

<MvELSE>

Table created successfully.


</MvIF>

</MvCOMMERCE>

<MvCOMMENT> insert a record </MvCOMMENT>

<MvASSIGN name = "sql"
value = "insert into mytable (name) values ('Homer Simpson')">

<MvCOMMERCE metamethod = "mysqlcommerce"
fields = "username,password,database,sql">

<MvIF expr = "{ mysql_error }">

Error inserting record!

<MvCOMMENT> ... error handling ... </MvCOMMENT>
<MvEXIT>

<MvELSE>

Record inserted successfully.


</MvIF>

</MvCOMMERCE>

<MvCOMMENT> select records (mvcommerce loops for each record found)
</MvCOMMENT>

<MvASSIGN name = "sql"
value = "select * from mytable">

<MvCOMMERCE metamethod = "mysqlcommerce"
fields = "username,password,database,sql">

<MvIF expr = "{ mysql_error }">

Error selecting records!

<MvCOMMENT> ... error handling ... </MvCOMMENT>
<MvEXIT>

<MvELSE>

Found record: id=&[id],name=&[name];


</MvIF>

</MvCOMMERCE>

<MvCOMMENT> delete records </MvCOMMENT>

<MvASSIGN name = "sql"
value = "delete from mytable where name = 'Homer Simpson'">

<MvCOMMERCE metamethod = "mysqlcommerce"
fields = "username,password,database,sql">

<MvIF expr = "{ mysql_error }">

Error deleteing records!

<MvCOMMENT> ... error handling ... </MvCOMMENT>
<MvEXIT>

<MvELSE>

Successfully deleted &[mysql_affected_rows]; records.


</MvIF>

</MvCOMMERCE>

<MvCOMMENT> drop test table </MvCOMMENT>

<MvASSIGN name = "sql"
value = "drop table mysql">

<MvCOMMERCE metamethod = "mysqlcommerce"
fields = "username,password,database,sql">

<MvIF expr = "{ mysql_error }">

Error dropping table!

<MvCOMMENT> ... error handling ... </MvCOMMENT>
<MvEXIT>

<MvELSE>

Table dropped successfully.


</MvIF>

</MvCOMMERCE>

<MvCOMMENT> drop test database </MvCOMMENT>

<MvASSIGN name = "sql"
value = "drop database commercetest">

<MvCOMMERCE metamethod = "mysqlcommerce"
fields = "username,password,sql">

<MvIF expr = "{ mysql_error }">

Error dropping database!

<MvCOMMENT> ... error handling ... </MvCOMMENT>
<MvEXIT>

<MvELSE>

Database dropped successfully.


</MvIF>

</MvCOMMERCE>

The speed test I have up is just a read-only straight read test on a 5k
record table, save for the first complex query. Depending on server load
(it's hosted on a shared server with about 30 other sites) it breaks down
like this: MySQL ~4-5 seconds, xBase ~5-6 seconds, ascii ~8-10 seconds.
It's a silly test, it's just to show that it works.

-Eric de Groot
mailto:[email protected]

----- Original Message -----
From: "Bob Hamilton" <[email protected]>
To: <[email protected]>
Sent: Tuesday, May 22, 2001 6:00 AM
Subject: [meu] MySQL vs XBASE


>
>
> I am intrigued by the use of MySQL. I currently use the XBASE interface
> included with Miva. My typical database has about 50,000 records and 2
> independent indexes (some are 2 level indexes using field pairs). I have
never
> had a problem with performance, but still would like to get the most out
of the
> applications I write. What are the general advantages of using MySQL over
> XBASE? And what are the performance advantages? (Any benchmark data
available
> between the two?)
>
> Bob
>
>

MySQL vs XBASE





I realize the syntax and format is different. And certainly there is a lot of
flexibility that goes with SQL access.
But if XBASE fills all of my needs now, is there any advantage to going to
MySQL?

Does it provide better scalability? Does it provide shorter access times? Does
it perform better?
Unless I have read it wrong, it seems to also be plagued by 256 byte field
lengths and key limitations.

Why has everyone else gone to SQL in lieu of XBASE?
I looked at www.mysql.com and they only compare against other SQL type
databases.
I can't seem to find a comparison of these 2 anywhere.

I want my site to be scalable (just in case I am successful), but does SQL
actually buy this for me?

Bob

MySQL vs XBASE



hi Bob,

You can't really compare a real SQL database like MySQL, Oracle, DB2, MS
SQL, JDBC with Miva's dbIII. In terms of features, scalability, performance
and security they are (sorry!) far superior than dbIII - but at a much
higher price especially in terms of administration, programming and licenses
(well, except MySQL and Interbase).

If you have really big databases with hundred thousands or millions of
entries, and/or have run complicated queries (those that require more than
only one or two conditions), every SQL-solution will perform much much
faster. Erik's MvCOMMERCE librairy demonstrates this at a very small
example, which is, as far as I know, also still in beta. Only a short time
ago someone else posted a link with a Miva connection via PHP to a MySQL
server, and converted 50.000 entries in just a few seconds.

Other features that most SQL databases usually support are stored procedures
(functions that are executed in the database itself instead of in the
application logic, which is again much faster), triggers (again from inside
the database a function can be called - like sending an email alert or so -
if a special event occurs); result sets can be cached, replication,
transactions, better indices, clustering, spanning your databases over
several drives or servers etc. etc. etc.

Almost all of these things can be recreated in the application logic (your
Miva script), but evidently this blows it up quite a bit and is much slower
in its execution. Also, although dbIII has no problem to hold several
million rows of data and can be extremely fast to find one record in the
index, the loading time makes it still much slower than if you have a true
SQL server.

On the other hand, it is more complex to write queries in SQL, and the
administration of such a server, if something goes wrong, can easily become
a nightmare. And last not least, the costs... With the exception of MySQL
and Interbase, licenses for these programs are often very expensive. This by
itself might be a main obstacle for most users.

For running a storefront with a couple of thousand products and some orders
every x-minutes, dbIII really does a good job. The data is mainly static,
there aren't many changes (write operations), and that's fine. If you start
to record and analyze customer patterns in real time, build datamines or if
you have 5000 connected users at the same time etc. you'll certainly have a
hard time with dbIII.

I believe that Erik's and Alan Runfeld's MySQL Mivacommerce-library is going
to be a great advancement for Miva, because it offers the best of both
worlds. Hopefully in future versions Empresa will feature a direct support
for SQL-databases, in ADDITION to the classic dbIII. If not, well, I guess
the ASP-,PHP-,Java-, XML- and coldfusion- guys & gals will expand their
advantage to a point that we can really forget about establishing Miva
Script as a "serious" language.

Markus Gieppner
MGF International inc.

By the way, MySQL has actually only some of the above mentioned features,
but what makes it actually one of the fastest databases on the market - For
Free! This even convinced NASA, they recently switched to MySQL.








-----Original Message-----
From: [email protected] [mailto:[email protected]]On
Behalf Of Bob Hamilton
Sent: Tuesday, May 22, 2001 6:19 PM
To: [email protected]
Subject: Re: [meu] MySQL vs XBASE




I realize the syntax and format is different. And certainly there is a lot
of
flexibility that goes with SQL access.
But if XBASE fills all of my needs now, is there any advantage to going to
MySQL?

Does it provide better scalability? Does it provide shorter access times?
Does
it perform better?
Unless I have read it wrong, it seems to also be plagued by 256 byte field
lengths and key limitations.

Why has everyone else gone to SQL in lieu of XBASE?
I looked at www.mysql.com and they only compare against other SQL type
databases.
I can't seem to find a comparison of these 2 anywhere.

I want my site to be scalable (just in case I am successful), but does SQL
actually buy this for me?

Bob

MySQL vs XBASE





Thanks for the basic info !

It gives me some insight into things I will need to learn. My database
requirements are fairly simple (I maintain a database of counts based on
demographics) and although the records are updated in place many times to add to
the counts, the records themselves stay static with no record length changes
(that I know of). The simple 2 index structure seems to be work well within the
bounds of the current structure (I also have a user database and notepad
database).

But I would like to learn more about SQL and will be interested in working with
whatever interface you all come up with. I assume that my users will eventually
want to go at the data from many different directions and I suppose it will be
in my best interest to accommodate them.

Thanks so much for your reply.

Bob