Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Re: How to count rows using DBI & mysql - Performance results

by Zaxo (Archbishop)
on Jun 11, 2001 at 17:43 UTC ( #87469=note: print w/ replies, xml ) Need Help??


in reply to How to count rows using DBI & mysql - Performance results

Declare your unique index to be a primary key. $sth->execute will fail if you try to insert a duplicated primary key.

mysql> create table trial (id varchar(5) primary key, perl_mod varchar +(30), summary varchar(255)); Query OK, 0 rows affected (0.06 sec) mysql> insert into trial set id='aaaaa', > perl_mod='CGI', > summary='Essential'; Query OK, 1 row affected (0.04 sec) mysql> insert into trial set id='aaaab', > perl_mod='DBI', > summary='Essential'; Query OK, 1 row affected (0.00 sec) mysql> insert into trial set id='aaaab', > perl_mod='Lingua__Romana__Perligata', > summary='Fun'; ERROR 1062: Duplicate entry 'aaaab' for key 1 mysql>

After Compline,
Zaxo


Comment on Re: How to count rows using DBI & mysql - Performance results
Download Code
Re: Re: How to count rows using DBI & mysql - Performance results
by hding (Chaplain) on Jun 11, 2001 at 17:51 UTC

    If adopting this strategy, why not simply make it a unique index, as it may in fact not be the primary key of the table?

Re: Re: How to count rows using DBI & mysql - Performance results
by Brovnik (Hermit) on Jun 11, 2001 at 18:45 UTC
    But I want to be able to have multiple rows with the same key/column1,
    Unique keys prevent that.
    --
    Brovnik

      Then why do you need to do an "INSERT ... IF NOT EXISTS"? (Note that if you're trying to enforce the uniqueness of some combination of columns, you can make the index on all of the columns; maybe this is what you mean.)

      hding is right. In that case, declare a multicolumn unique index. That does precisely what you are asking for. MySQL will look at the first column first, so it should be the more unique. [OB] A table needs a primary key.

      create table depends (uses varchar(80) not null, usedby varchar(80) not null, unique deps (usedby,uses)); # naming the index is unnecessary, but may be a convenience

      Btw, why not represent a leaf node by usedby=NULL? That would give a faster select for leaf/branch.

      After Compline,
      Zaxo

      In this case you should have a one to many foreign key relationship to a second table. This table would contain a unique column with all of your modules in it. Insert into this table, catch the exception if it happens.
Re: Re: How to count rows using DBI & mysql - Performance results
by jepri (Parson) on Jun 11, 2001 at 20:20 UTC
    This trick has caused me problems. At one point I had Tie::DBI crashing when it got an error from the POSTGRESQL database saying the the database couldn't insert a row. I don't know why, but I had to work around by doing the SQL myself :(

    Also, talking coding style, finding out if you can do something by seeing if it throws an error is probably a worse option than checking for it cleanly in the first place.

    ____________________
    Jeremy
    I didn't believe in evil until I dated it.

      My point was to demonstrate the property of unique indexes which prevent duplication: a safety measure. I never said to use the error for program control. In fact I didn't exhibit any DBI code at all.

      In practice, with the index in place:

      my $known_dep=$dbi->prepare_cached("select 1 from depends where usedby +=? and uses=?"); sub known_dep ($$) {#prototype because we have a fixed number of place +holders $known_dep->execute(@_); $known_dep->rows; # 1 or 0 for unique index. }

      For frequent operations, we can improve performance with the sth->bind_params() method.

      We search on an index, using a pre-prepared statement handle with placeholders. That will be fast.

      I didn't mention before that NULL in multiindexes is only available in MyISAM tables.

      After Compline,
      Zaxo

      It is definitly a coding style question, because we do it all over the place with PL/SQL in Oracle. Though this would not generate an error, just an exception. We use the same technique to see if a query a row from cache, if it returns an exception then we hit the table instead.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://87469]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (8)
As of 2014-12-25 10:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (160 votes), past polls