Hi, I have a table containing logfiles in mysql v4.0, myISAM. The table has about 8.5 million records. I'm using the my_huge.cnf file on mandrake10 Linux with 1 gig ram and 250gig HD space. Some Info: Space usage : Type Usage Data 3,063 MB Index 660,855 KB Total 3,708 MB
Row Statistic : Statements Value Format dynamic Rows 8,781,134 Row length ø 365 Row size ø 443 Bytes Next Autoindex 8,781,135 Creation Oct 14, 2004 at 09:23 PM Last update Oct 20, 2004 at 11:57 AM Last check Oct 14, 2004 at 09:34 PM Indexes : Keyname Type Cardinality Field PRIMARY PRIMARY 8781134 id originalID UNIQUE 8781134 originalID databaseName INDEX 9 databaseName origID INDEX 8781134 origID destinationcode INDEX 8625 destinationcode finaldestination INDEX 2195283 finaldestination datetime INDEX 8781134 datetime Normally i'd like to be able to get statistics from the database in 1month chunks (about 1 million records) but if I do a: SELECT count( * ) FROM `table` WHERE datetime BETWEEN '2004-09-01 00:00:00' AND '2004-10-01 00:00:00' It will return the count: 1372668, but it takes 2 or 3 minutes to do this. If I add any other (indexed) criteria it becomes even slower. I have noticed if I just request 1 or 2 days' records, the result comes fast, but once the count gets over 100000 or so, everything slows down. My own solution at the moment is to make temporary tables for each month, as things seem to stay fast with less than 2mil. records in a table. Does anyone have any advice on how to optimise this setup? Thanks, Richard. PS, some extra mysql info: (sorry for the long post) Server variables and settings Variable Global value back log 50 basedir / binlog cache size 32768 bulk insert buffer size 8388608 character set latin1 character sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent insert ON connect timeout 5 convert character set datadir /var/lib/mysql/ default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1000 flush OFF flush time 0 ft boolean syntax + -><()~*:""&| ft min word len 4 ft max word len 254 ft max word len for sort 20 ft stopword file (built-in) have bdb NO have crypt YES have innodb YES have isam YES have raid NO have symlink YES have openssl NO have query cache YES init file innodb additional mem pool size 1048576 innodb buffer pool size 8388608 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb file io threads 4 innodb force recovery 0 innodb thread concurrency 8 innodb flush log at trx commit 1 innodb fast shutdown ON innodb flush method innodb lock wait timeout 50 innodb log arch dir ./ innodb log archive OFF innodb log buffer size 1048576 innodb log file size 5242880 innodb log files in group 2 innodb log group home dir ./ innodb mirrored log groups 1 innodb max dirty pages pct 90 interactive timeout 28800 join buffer size 131072 key buffer size 402653184 language /usr/share/mysql/english/ large files support ON local infile ON locked in memory OFF log OFF log update OFF log bin ON log slave updates OFF log slow queries OFF log warnings OFF long query time 10 low priority updates OFF lower case table names 0 max allowed packet 1047552 max binlog cache size 4294967295 max binlog size 1073741824 max connections 100 max connect errors 10 max delayed threads 20 max heap table size 16777216 max join size 4294967295 max relay log size 0 max seeks for key 4294967295 max sort length 1024 max user connections 0 max tmp tables 32 max write lock count 4294967295 myisam max extra sort file size 268435456 myisam max sort file size 2147483647 myisam repair threads 1 myisam recover options OFF myisam sort buffer size 67108864 net buffer length 16384 net read timeout 30 net retry count 10 net write timeout 60 new OFF open files limit 1024 pid file /var/lib/mysql/server.pid log error port 3306 protocol version 10 query alloc block size 8192 query cache limit 1048576 query cache size 33554432 query cache type ON query prealloc size 8192 range alloc block size 2048 read buffer size 2093056 read only OFF read rnd buffer size 262144 rpl recovery rank 0 server id 1 slave net timeout 3600 skip external locking ON skip networking OFF skip show database OFF slow launch time 2 socket /var/lib/mysql/mysql.sock sort buffer size 2097144 sql mode 0 table cache 457 table type MYISAM thread cache size 8 thread stack 196608 tx isolation REPEATABLE-READ timezone CEST tmp table size 33554432 tmpdir /home/rich/tmp/ transaction alloc block size 8192 transaction prealloc size 4096 version 4.0.18-log version comment Source distribution wait timeout 28800
