Help querying a simple but large table

[Dan Makovec]

Hi all,   I'm having problems getting reasonable speed queries from a simple table.   First, a little info about my server setup:   From mysqladmin version: ---------------------------------------------------------------------------------------- mysqladmin  Ver 7.11 Distrib 3.22.27, for -freebsd3.4 on i386 TCX Datakonsult AB, by Monty   Server version          3.22.30 Protocol version        10 Connection              Localhost via UNIX socket UNIX socket             /tmp/mysql.sock ----------------------------------------------------------------------------------------   I'm running on a P166 with 96MB of RAM, with FreeBSD 3.4 dedicated to MySQL with no other load impeding tasks.   I've created a simple table that contains stock exchange data, as follows:   ---------------------------------------------------------------------------------------- CREATE TABLE shares (   date date DEFAULT '0000-00-00' NOT NULL,   symbol char(10) DEFAULT '' NOT NULL,   open float(10,2) DEFAULT '0.00' NOT NULL,   close float(10,2) DEFAULT '0.00' NOT NULL,   high float(10,2) DEFAULT '0.00' NOT NULL,   low float(10,2) DEFAULT '0.00' NOT NULL,   volume int(11) DEFAULT '0' NOT NULL,   PRIMARY KEY (date,symbol),   KEY symbol_index (symbol(4)),   KEY symbol_date_index (symbol(4),date) ); ----------------------------------------------------------------------------------------   To verify the indexes: ---------------------------------------------------------------------------------------- mysql> show index from shares; +--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+ | Table  | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+ | shares |          0 | PRIMARY           |            1 | date        | A         |     5791651 |     NULL | | shares |          0 | PRIMARY           |            2 | symbol      | A         |     5791651 |     NULL | | shares |          1 | symbol_index      |            1 | symbol      | A         |        NULL |        4 | | shares |          1 | symbol_date_index |            1 | symbol      | A         |        NULL |        4 | | shares |          1 | symbol_date_index |            2 | date        | A         |        NULL |     NULL | +--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+ ----------------------------------------------------------------------------------------   And I've filled this with a bit of data.  Well, more than a bit - 5,791,651 rows actually.  Still, while this is large, it doesn't seem rediculously huge.   Let's say I want to perform a query to select the last 22 days worth of closing prices for stock ABC.  I run the query:   ---------------------------------------------------------------------------------------- mysql> select close from shares where symbol='abc' and date < current_date() order by date desc limit 22; ---------------------------------------------------------------------------------------- ... Data eventually received ...   ---------------------------------------------------------------------------------------- 22 rows in set (12.23 sec) ----------------------------------------------------------------------------------------   Now, to me that seems very slow.  And this is a fairly good result for an uncached symbol - some queries of the same structure but using a different symbol have taken up to 30 seconds, with the server under no load other than this single connection to MySQL.  Am I being unrealistic with my expectations of speed, or is there a way to optimize this?   I ran "optimize table shares", which took 4 hours, and it's no faster.   Incidentally, "explain" gives me the following:   ---------------------------------------------------------------------------------------- mysql> explain select close from shares where symbol='abc' and date < current_date() order by date desc limit 22; +--------+------+----------------------------------------+--------------+---------+------+------+-------+ | table  | type | possible_keys                          | key          | key_len | ref  | rows | Extra | +--------+------+----------------------------------------+--------------+---------+------+------+-------+ | shares | ref  | PRIMARY,symbol_index,symbol_date_index | symbol_index |       4 | abc  | 4475 |       | +--------+------+----------------------------------------+--------------+---------+------+------+-------+ ----------------------------------------------------------------------------------------   And benchmarking gives me: ---------------------------------------------------------------------------------------- mysql> select benchmark(1000000,1+1); 1 row in set (0.83 sec) ---------------------------------------------------------------------------------------- I'm running the server with the arguments: safe_mysqld --user=$mysql_daemon_user -O key_buffer=16M -O sort_buffer=1M --pid-file=$pid_file --datadir=$datadir   Does anybody have any idea how I can get better results (short of upgrading the server) ?  Could you please respond directly to me, as I'm not subscribed to this list.   Thanks in advance, D. --- Dan Makovec Fat Canary Software ICQ: 1398090 Email: [EMAIL PROTECTED]

smime.p7s