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

[Zaxo]

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

[hding]

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?

[Brovnik]

But I want to be able to have multiple rows with the same key/column1,
Unique keys prevent that.
--
Brovnik

[hding]

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.)

[Zaxo]

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

[elwarren]

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.

[jepri]

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.

[Zaxo]

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

[elwarren]

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.