Analyzing a table requires looking at every record in the table. If the table you are dealing with is a large one then there is a good chance that after analyzing the table your disk cache will not contain the records that you are trying to grab. Once you run the query it will almost certainly contain those records. You could test this theory by rebooting your machine (if that is an option) and seeing if it takes the longer amount of time for the first query.
Or I could be completely off base. It wouldn't be the first time. :-) Jon Gardiner. > -----Original Message----- > From: Bill Adams [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, November 13, 2001 2:47 PM > To: Mysql List > Subject: myisamchk -a + indexes + hidden...? > > > It seems like myisamchk -a is hosing some > statistic in a MyISAM table that gets re-created > and stored permanently as once a query is run that > uses that index, it always runs well until > myisamchk -a is run again even between restarts of > mysqld. It also seems that key_buffer_size has no > effect on the results. > > Can someone explain this to me? > > There are a a bunch of tables merged into three > main tables. The query does a two column join > between the tables, e.g.: a.1=b.1 AND a.2=b.2 AND > b.1=c.1 AND b.3=c.3 > > --Bill > > > [root@host /usr/local/mysql-4.0/var]# > ../bin/mysqladmin -uroot -p version > ../bin/mysqladmin Ver 8.22 Distrib 4.0.0-alpha, > for pc-linux-gnu on i686 > Copyright (C) 2000 MySQL AB & MySQL Finland AB & > TCX DataKonsult AB > This software comes with ABSOLUTELY NO WARRANTY. > This is free software, > and you are welcome to modify and redistribute it > under the GPL license > > Server version 4.0.0-alpha-log > Protocol version 10 > Connection Localhost via UNIX socket > UNIX socket /tmp/mysql-4.0.sock > Uptime: 7 min 2 sec > > Threads: 3 Questions: 103 Slow queries: 0 > Opens: 12 Flush tables: 46 Open tables: 3 > Queries per second avg: 0.244 > [root@host /usr/local/mysql-4.0/var]# uname -a > Linux host.tqs.com 2.2.19 #6 SMP Wed Jul 11 > 10:55:03 PDT 2001 i686 unknown > [root@host /usr/local/mysql-4.0/var]# vmstat > procs memory > swap io system cpu > r b w swpd free buff cache si so > bi bo in cs us sy id > 2 1 0 4 2612 40752 1450688 0 0 > 4 3 3 6 10 2 6 > [root@host /usr/local/mysql-4.0/var]# ldd > ../libexec/mysqld > librt.so.1 => /lib/librt.so.1 (0x2aac8000) > > libdl.so.2 => /lib/libdl.so.2 (0x2aacc000) > > libpthread.so.0 => /lib/libpthread.so.0 > (0x2aad0000) > libz.so.1 => /usr/lib/libz.so.1 > (0x2aae3000) > libcrypt.so.1 => /lib/libcrypt.so.1 > (0x2aaf3000) > libnsl.so.1 => /lib/libnsl.so.1 > (0x2ab20000) > libstdc++-libc6.1-1.so.2 => > /usr/lib/libstdc++-libc6.1-1.so.2 (0x2ab36000) > libm.so.6 => /lib/libm.so.6 (0x2ab78000) > libc.so.6 => /lib/libc.so.6 (0x2ab95000) > /lib/ld-linux.so.2 => /lib/ld-linux.so.2 > (0x2aaab000) > [root@host /usr/local/mysql-4.0/var]# > > > myisamchk -a (on all tables) > set-variable = key_buffer=32M > Time to start getting results: 127 seconds. > Total Time: 146 seconds (18251 rows, 125 rows/sec > overall) > > Run the query again: > Time to start getting results: 11 seconds. > Total Time: 30 seconds (18251 rows, 608 rows/sec) > > shutdown mysql > set-variable = key_buffer=1M > start mysql > myisamchk -a (on all tables) > Time to start getting results: 121 seconds. > Total Time: 141 seconds (18251 rows, 129 rows/sec) > > Second Run: > > Run the query again: > Time to start getting results: 10 seconds. > Total Time: 29 seconds (18251 rows, 629 rows/sec) > > > Shutdown and restart MySQL. > Note: key_buffer still at 1M > Time to start getting results: 12 seconds. > Total Time: 31 seconds (18251 rows, 588 rows/sec) > > Shutdown MySQL > Set key buffer size to 0 > start mysql > Time to start getting results: 10 seconds. > Total Time: 29 seconds (18251 rows, 629 rows/sec) > > myisamchk -a > Time to start getting results: 145 seconds. > Total Time: 164 seconds (18251 rows, 111 rows/sec) > > > > -- > Bill Adams > TriQuint Semiconductor > > > > > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
