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
