Pascal THIVENT writes:
> Hi,
>
> the database I would like to managed is constitued by one table.
> This table is made of 3 columns, with no primary key
>
> mysql> CREATE TABLE test (
> id1 int(8) unsigned,
> id2 int(4) unsigned,
> id3 int(4) unsigned
> );
>
> mysql> CREATE INDEX idx_id1 ON test (id1);
> mysql> CREATE INDEX idx_id2 ON test (id2);
> mysql> CREATE INDEX idx_id3 ON test (id3);
>
> We use 3 indexes because our benchmark shown that it's better than an index
> on the 3 columns for our application.
>
>
> mysql> desc test;
> +-----------------+-----------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------------+-----------------+------+-----+---------+-------+
> | id1 | int(8) unsigned | | MUL | 0 | |
> | id2 | int(4) unsigned | | MUL | 0 | |
> | id3 | int(4) unsigned | | MUL | 0 | |
> +-----------------+-----------------+------+-----+---------+-------+
>
>
> Here are the request we make on this table :
>
> SELECT COUNT(*) FROM test WHERE id1=? AND id2=?;
> SELECT COUNT(*) FROM test WHERE id1=? AND id=2=? AND id3=?;
> INSERT INTO test (USER_ID, FLIGHT_ID, FLIGHTOBJECT_ID) values (?,?,?);
>
>
> The row size is 16 bytes. So we'll get 16 gigabytes of datas per day (it
> will be around 10 gigabyte in reality because only 66% of the request need
> an insert).
>
> The table is going to grow until we'll drop it (because of storage
> limitations). 10 days of lifetime for our datas will be enough :)
> We make no update in order to be very quick.
Hi!
Consider using smallint instead of int for second and third column.
This will make entire row size 8 bytes. That is for data only, not
counting index space, which is more complicated.
You make no update in order to be quick. But may be with updates your
table could last longer. Much longer.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
