Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: MySQL: integrity constrain

    I'm looking for a DB server that ensure the intetrity constrain over the DB. (PK/FK)

    Is MySQL such a DB ?

    H2O

  2. #2
    Join Date
    Sep 2003
    Location
    CA
    Posts
    38

    Re: MySQL: integrity constrain

    For integrity constraints I guess Oracle is really powerful.
    Pooja

    Originally posted by H2O
    I'm looking for a DB server that ensure the intetrity constrain over the DB. (PK/FK)

    Is MySQL such a DB ?

    H2O

  3. #3
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: MySQL: integrity constrain

    Foreign Keys are not supported in MySQL

    Originally posted by H2O
    I'm looking for a DB server that ensure the intetrity constrain over the DB. (PK/FK)

    Is MySQL such a DB ?

    H2O

  4. #4
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    MySql can support Foreign Keys and other constraint with innodb table type

    See http://www.lasso-developpeur.net/us/...tab=db&lang=us
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  5. #5
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    Yes, you're right . The syntax is at:
    http://www.mysql.com/doc/en/InnoDB_f...nstraints.html

    Originally posted by omiossec
    MySql can support Foreign Keys and other constraint with innodb table type

    See http://www.lasso-developpeur.net/us/...tab=db&lang=us

  6. #6
    Join Date
    Oct 2003
    Posts
    24

    Unhappy

    It seems to be a bit complicated:
    (at least it is not an SQL standard to have an index explicitaly defined to obtain the referential integrity.)

    ------------------------------
    The syntax of a foreign key constraint definition in InnoDB:

    [CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
    REFERENCES table_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]
    Both tables have to be InnoDB type, in the table there must be an INDEX where the foreign key columns are listed as the FIRST columns in the same order, and in the referenced table there must be an INDEX where the referenced columns are listed as the FIRST columns in the same order. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. The indexes are needed for foreign key checks to be fast and not require a table scan.
    ---------------------------------

    The other table type to support referential integrity seems to be BDB (Berkley DB), and it seems also not to have such a strange definitions to achieve the goal.
    The more strange is that, with innoDB the mysql is far more quick, but the standard tables are not the innoDB, but the MyIsam ones....

    And again to get the InnoDb tables you have to use a non ANSI SQL (not portable) predicate into the CREATE statement (Table=InnodB).


    Disappointing....


    H2O

  7. #7
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    If you want to make your code portable, you could write a create table statement with the type wrapped in a comment like this:

    CREATE TABLE test ( <column definitions> ) /*!Type=InnoDB*/;

    MySQL will parse a comment started with "/*!" as part of the issued statement, but other SQL servers will ignore it.

    Why do you think that InnoDB tables are faster than MyISAM? MyISAM does not have the same overhead that InnoDB does. MySQL AB recommends using MyISAM and other non-transaction-safe tables for speed.

    Originally posted by H2O
    It seems to be a bit complicated:
    (at least it is not an SQL standard to have an index explicitaly defined to obtain the referential integrity.)

    ------------------------------
    The syntax of a foreign key constraint definition in InnoDB:

    [CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
    REFERENCES table_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]
    Both tables have to be InnoDB type, in the table there must be an INDEX where the foreign key columns are listed as the FIRST columns in the same order, and in the referenced table there must be an INDEX where the referenced columns are listed as the FIRST columns in the same order. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. The indexes are needed for foreign key checks to be fast and not require a table scan.
    ---------------------------------

    The other table type to support referential integrity seems to be BDB (Berkley DB), and it seems also not to have such a strange definitions to achieve the goal.
    The more strange is that, with innoDB the mysql is far more quick, but the standard tables are not the innoDB, but the MyIsam ones....

    And again to get the InnoDb tables you have to use a non ANSI SQL (not portable) predicate into the CREATE statement (Table=InnodB).


    Disappointing....


    H2O

  8. #8
    Join Date
    Oct 2003
    Posts
    24
    Why do you think that InnoDB tables are faster than MyISAM? MyISAM does not have the same overhead that InnoDB does. MySQL AB recommends using MyISAM and other non-transaction-safe tables for speed. [/SIZE]
    http://www.innodb.com/bench.html
    InnoDB and MyISAM table types in MySQL
    I ran the tests on a Linux 2-CPU Xeon 450 MHz. The times below are wall clock times. The results were:

    InnoDB MyISAM
    ------------------------------------------------------------
    100 000 inserts 25 s. 40 s.

    100 000 selects on
    primary key 57 s. 58 s.

    100 000 selects on
    secondary key 68 s. 95 s.
    ------------------------------------------------------------


    Consider also the benefit that could come also from all theese features:
    http://www.innodb.com/howtouse.html

    H2O
    P.S:
    By the way: the perf. bechmark publisced by e-week was of course with MySql+InnoDB.......

  9. #9
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    I have run some benchmarks of my own that do not look anything like that. Using a simple table structure and a simple loop in Python to insert 10 000 rows, the InnoDB table type is at least 5 times slower. I got results of a little over 7 sec for MyISAM and a little more than 39 sec for InnoDB on my PIII 500. Take a look at the python code that I have included. Also, in the eWeek article, the team chose InnoDB only when the spec required transaction-safe tables. They chose MyISAM tables everywhere else.

    Originally posted by H2O
    http://www.innodb.com/bench.html
    InnoDB and MyISAM table types in MySQL
    I ran the tests on a Linux 2-CPU Xeon 450 MHz. The times below are wall clock times. The results were:

    InnoDB MyISAM
    ------------------------------------------------------------
    100 000 inserts 25 s. 40 s.

    100 000 selects on
    primary key 57 s. 58 s.

    100 000 selects on
    secondary key 68 s. 95 s.
    ------------------------------------------------------------


    Consider also the benefit that could come also from all theese features:
    http://www.innodb.com/howtouse.html

    H2O
    P.S:
    By the way: the perf. bechmark publisced by e-week was of course with MySql+InnoDB.......
    Attached Files Attached Files

  10. #10
    Join Date
    Oct 2003
    Posts
    24
    [SIZE=1]Originally posted by aus
    I have run some benchmarks of my own that do not look anything like that.
    ahhh interesting ....
    the power of InnodDB marketing .... quite disappointing...
    May be the difference stay with the use of indexes.... ?

    quoting from ... http://www.innodb.com/bench.html
    "
    ...
    I wrote a Perl program which inserts 100 000 rows to a table with 3 integer columns and two indexes. Then another Perl program fetches each row either through a secondary index or the primary key.
    The Perl programs for each test are at the end of this web page.
    ".

    In peformace analisis, indexes playe a BIG role...
    I'm sorry not having php installed (i'm just a beginner with Mysql...) but
    I suggest a db example, to find if the peformance in and indexed Innodb database overcome the MyIsam perfomance:

    Database:
    a three fields database (
    ID-counter-pk,
    ind- integer- indexed
    data- varchar - )

    We (you ) can Create the db, setting the ind field to the recnum.
    Then, acces them (better randomly, but I think is the same as you acces them by an indexed field...) and update the data value.

    May the perfomance will change ?



    I got results of a little over 7 sec for MyISAM and a little more than 39 sec for InnoDB on my PIII 500.
    Can you make me a favor ?
    If you got the MS MSDE engine (the "open" SQL server, Desktop edition) can you run the same test with it, just to have an idea of which are the perf. differences between the two referential-constrain solution ....(InnoDB vs MSDE).

    I would very apprechiate ....

    H2O

  11. #11
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    I ran the test like you specified and got some interesting results. The two table types performed neck and neck. Sometimes InnoDB was in front and other times MyISAM was faster. The difference was almost never more than one-tenth of a second. The record selected was random, but in the same order for the two tables. I did not use the recnum for ind, but rather 10000 - recnum. When I changed the table definition from varchar(24) to char(24), MyISAM came out ahead of InnoDB by two-tenths of a second every time. This shows that a variable length column affects MyISAM more than InnoDB. It goes along with what MySQL AB has to say about performance, since they recommend fixed-length columns whenever possible.

  12. #12
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    I can't test SQL Server at the moment, but maybe I can later in the week.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •