From: Michael Widenius Date: March 17 1999 9:33am Subject: Performance And, What am I Missing List-Archive: http://lists.mysql.com/mysql/401 Message-Id: <14063.29574.308645.649110@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >>>>> "Van" == Van writes: Van> Okay, Monty, Van> About a week ago, someone had posted something with respect to a huge database Van> they wanted to use MySQL for, and I responded with some stats I'd come up with on Van> a 2.5 Million row database that was about a 200 Meg Data file that took about 60 Van> seconds to syphon through, and, you'd mentioned that you can query even larger databases Van> on a 486 with 16 Meg RAM in less than seconds. I believe you, but, I'm not getting this Van> kind of performance on the following table. Before I start: Before tonight, all but the Van> 1st field was defined as accepting of NULL. Originally created in 3.21.13, and has been Van> migrated along the way. The table is attached to quite a few PHP 3.06 web pages to poll Van> client data when they hit the page. Essentially, I don't have issues with the table staying Van> up to date. Issues I have are with querying the table to determine activity. At the command Van> prompt, or otherwise (i.e., Mysql-WebAdmin/PhPAdmin;Kmysqladmin;Xmysqladmin). Slow queries. Van> It all starts with: Van> /usr/local/libexec/mysqld --user=mysql -l/usr/local/var/tempe.log \ Van> -O key_buffer=16M -O table_cache=128 -O sort_buffer=8M \ Van> -O record_buffer=4M --big-tables & Van> I'm running Linux Slackware 3.6 with kernel 2.0.34. (I'm also running another kernel 2.2.2 that Van> exhibits the same types of performance. Ram is at 64 Meg; filesystems is unlimited for practical Van> And, newstats does use up the entire space on many records in the table, which shouldn't matter. Van> And, now the query: mysql> select distinct ScriptFilename from newstats; 47 rows in set (7.55 sec) Van> And 7.55 secs seems pretty long when the table is: mysql> select count(*) from newstats; Van> +----------+ Van> | count(*) | Van> +----------+ Van> | 38800 | Van> +----------+ Van> 1 row in set (0.00 sec) Van> I've tried indexing on various rows but the queries still take this long. Van> On my larger tables/databases it can become quite time-consuming. Van> Perhaps I need to recompile with some different flags. Van> Reading specs from /usr/lib/gcc-lib/i486-unknown-linux-gnulibc1/2.7.2.3/specs Van> gcc version 2.7.2.3. Van> Anyway, I'm stumped. Hi! First note that my reply was the speed is depending on what kind of queries you are doing. For example, queries of type WHERE key_column=constant are fast with any hardware and any database size, while GROUP or DISTINCT on the whole table is depending on the size of the data file. How big is newstats.ISD and newstats.ISM in your case ? If you have a key on ScriptFilename, then MySQL must scan through the .ISM file. If not, then MySQL must scan through the .ISD file. If you are using MySQL 3.22.17 or newer, you can try to use: select /*! SQL_SMALL_RESULT */ distinct ScriptFilename from newstats; This should be somewhat faster than your original query. Regards, Monty