Jan, right now I would say performance sounds pretty good for the amount
of data you have. 50 million records / 2.5 GB of data is a pretty
sizable dataset, so 4 seconds to retrieve a handful of records seems decent.
Some suggestions for things you could do to possibly improve performance:
1 - Change to the compressed table format. See
http://dev.mysql.com/doc/refman/5.0/en/compressed-format.html and
http://techrepublic.com.com/5100-9592-5852557.html
This will in theory allow a given number of records to be read off disk
more quickly, since they are smaller.
2 - Run an ALTER TABLE command to ORDER BY the field you search against
most commonly. This will in theory allow the database engine to read
the table in a more orderly fashion without (as much) disk thrashing.
3 - upgrade memory in your server and change MySQL cache settings to
match, in an attempt to keep the indexes and data in RAM. With the
amount of data you have this should be possible but you'll need to make
sure your hardware, OS, and MySQL release support an appropriate amount
of RAM.
4 - if you can't do #3, and maybe even if you can, make sure you're
using a very fast disk system (I'd guess you already are). I'd look at
at a hardware RAID 5 or 1+0 setup on 15K RPM U320 SCSI disks. Fibre is
nice if you can get it but tends to get expensive quickly.
Of course #3 and #4 will be dependent on budget and importance of this
problem, not to mention your MySQL hosting setup/relationship.
Hope this helps,
Dan
Jan Gomes wrote:
Hy Guys,
I have a simple structure of tables, howewer has 50 million of registers and
2,5 GB of data.
The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields
and one btree index with one integer field.
There is a select in this table using an index(with one integer field), whith a set value
for this field ( select * from table where field in (value1,value2,value3,value4,etc) ).
This select has delay 4s average.
Is this a good time for the select ? How can I run this select in less time?
I had make this optimization:
1-Compress the index
2-sort the index with myisamchk
PS.: This table is read-only, hasn't an insert, update or delete.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
