>>>>> "Van" == Van <vanboers@stripped> 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
<cut>
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;
<cut>
47 rows in set (7.55 sec)
Van> And 7.55 secs seems pretty long when the table is:
<cut>
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
