I have a database with a table called targetTsObj, and I want to perform many queries from it, such as this one:
select run,rerun,camcol,field,id,ra,decl,r from targetTsObj where ra between 255.84730673785904 and 256.31969326214096 and decl between 58.749411111111108 and 58.957788888888892 One of these returns a few hundred records from the 1.2 million records in the table. This query returns in under a second. However, after 40 or so of these queries, the time per query is up to 30 seconds or so. Is this perfomance as expected, or is there something I can do? Build the indices differently? A setting in my.cnf? The machine is running Linux, with 1 GB or ram, MySQL server version 3.23.49 Thanks! =================================================================== I send the queries with a client program, using these C api's: mysql_real_connect(mysql, host, user, passwd, db, port, unix_socket, client_flag); mysql_query(mysql, query); mysql_store_result(mysql); In a loop, until all rows are fetched: field = mysql_fetch_fields(mysql_res); And after getting all the rows for a query: mysql_free_result(mysql_res); Here is the result of the "show index" command: mysql> show index from targetTsObj; +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | targetTsObj | 1 | objId | 1 | objId | A | 1228983 | NULL | NULL | | | targetTsObj | 1 | fieldId | 1 | fieldId | A | 1985 | NULL | NULL | | | targetTsObj | 1 | ra | 1 | ra | A | 1228983 | NULL | NULL | | | targetTsObj | 1 | decl | 1 | decl | A | 1228983 | NULL | NULL | | | targetTsObj | 1 | u | 1 | u | A | 111725 | NULL | NULL | | | targetTsObj | 1 | g | 1 | g | A | 122898 | NULL | NULL | | | targetTsObj | 1 | r | 1 | r | A | 122898 | NULL | NULL | | | targetTsObj | 1 | i | 1 | i | A | 122898 | NULL | NULL | | | targetTsObj | 1 | z | 1 | z | A | 111725 | NULL | NULL | | | targetTsObj | 1 | ug | 1 | u | A | 111725 | NULL | NULL | | | targetTsObj | 1 | ug | 2 | g | A | 1228983 | NULL | NULL | | | targetTsObj | 1 | gr | 1 | g | A | 122898 | NULL | NULL | | | targetTsObj | 1 | gr | 2 | r | A | 1228983 | NULL | NULL | | | targetTsObj | 1 | ri | 1 | r | A | 122898 | NULL | NULL | | | targetTsObj | 1 | ri | 2 | i | A | 1228983 | NULL | NULL | | | targetTsObj | 1 | iz | 1 | i | A | 122898 | NULL | NULL | | | targetTsObj | 1 | iz | 2 | z | A | 1228983 | NULL | NULL | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ 17 rows in set (0.00 sec) Finally, here is /etc/my.cnf: bash-2.04$ more /etc/my.cnf [mysqld] set-variable = key_buffer_size=512M set-variable = table_cache=512 set-variable = query_buffer_size=20M # set-variable = sort_buffer=100M # set-variable = read_buffer_size=100M datadir=/export/data/dp20.a/data/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --------------------------------------------------------------------- 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