Thanks everybody for attention! I make some changes in hardare and in my table, and obtain a average response times of 0.09 second.
Now i have a good time ! :-) Show you my changes: 1-Update MySql 4.1.18 -> 5.0.21. 2-Upgrade my memory to 3 Gb. 3-Severance of temporary/logs files and data files in distinct HDs. 4-Upgrade of key_buffer 384M -> 512M. 5-Run command ALTER TABLE ORDER BY on column.(Dan Suggestion. Thanks!!) I will reproduce this changes in other machine to detect the relevant change! Show you my relevant variable in config file: datadir = /data/myisam key_buffer = 512M max_allowed_packet = 1M table_cache = 200 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 innodb_data_home_dir = /data/ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/iblog innodb_log_arch_dir = /usr/local/mysql/data/iblog innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Show you my compile options: (OS: Debian Linux 3.1; Hardware Intel Xeon 2.8) CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti -fomit-frame-pointer -ffixed-ebp" ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static --enable-assembler Thanks everybody!! ============== Atenciosamente, Jan Gomes - [EMAIL PROTECTED] > > The query is: > > SELECT * FROM table > > where id_table2 in (1652272,1652293,1652316,1652362); > > > > +-------------+-------+-------+----------------------+ > > | select_type | table | type | possible_keys | > > +-------------+-------+-------+----------------------+ > > | SIMPLE | table | range | PRIMARY,table_idx1| > > +-------------+-------+-------+----------------------+ > > > > +------------+---------+------+------+--------------+ > > | key | key_len | ref | rows | Extra | > > +----------- +---------+------+------+--------------+ > > | table_idx1 | 4 | NULL | 72 | Using where| > > +------------+---------+------+------+-------------+ > > > > If this is what the EXPLAIN is, and you are getting 4 second response > times, I would guess that something is amiss. Have you tried running an > ANALYZE TABLE on your table to rebuild the index? Be aware that this > will issue a table lock on the table, do do it "off hours". > > Then, issue the query again and post back time results. > > Also, post the output of SELECT @@version; > > Cheers > > Jay > > > PS.: Pardon me for some thing, my english is not very good > > It's just fine! > > >>> Hy Jay, > >>> > >>> Thanks for you attention. > >>> > >>> Show you my structure and EXPLAIN: > >>> > >>> CREATE TABLE `table` ( > >>> `id_table1` int(10) unsigned NOT NULL default '0', > >>> `id_table2` int(10) unsigned NOT NULL default '0', > >>> `field1` smallint(5) unsigned NOT NULL default '0', > >>> `field2` mediumint(8) unsigned NOT NULL default '0', > >>> `textField` text NOT NULL, > >>> > >>> PRIMARY KEY (`id_table1`,`id_table2`), > >>> KEY `table_idx1` (`id_table2`) > >>> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 > >>> > >>> > >>> > >>> > >> You don't show the query you are explaining. > >> It certainly can't be the one in your previous post below, because column > >> names don't match. > >> > >>> +-------------+-------+-------+--------------------+ > >>> | select_type | table | type | possible_keys | > >>> +-------------+-------+-------+--------------------+ > >>> | SIMPLE | table | range | PRIMARY,table_idx1| > >>> +-------------+-------+-------+--------------------+ > >>> > >>> +------------+---------+------+------+-------------+ > >>> | key | key_len | ref | rows | Extra | > >>> +----------- +---------+------+------+-------------+ > >>> | table_idx1 | 4 | NULL | 72 | Using where| > >>> +------------+---------+------+------+-------------+ > >>> > >>> > >>> > >>>> Please post your exact table schema using SHOW CREATE TABLE, and your > >>>> exact query, along with an EXPLAIN SELECT for the query. > >>>> > >>>> Thanks! > >>>> > >>>> -jay > >>>> > >>>> Jan Gomes wrote: > >>>> > >>>> > >>>>> Hy Guys, > >>>>> > >>>>> I have a simple structure of tables, howewer has 50 million of > >>>>> registers and 2,5 GB of data. > >>>>> The table is MyIsam and has 4 integer fields and 1 Text field, one > >>>>> primary key with two integer fields > >>>>> and one btree index with one integer field. > >>>>> > >>>>> There is a select in this table using an index(with one integer field), > >>>>> whith a set value > >>>>> for this field ( select * from table where field in > >>>>> (value1,value2,value3,value4,etc) ). > >>>>> > >>>>> This select has delay 4s average. > >>>>> > >>>>> Is this a good time for the select ? How can I run this select in less > >>>>> time? > >>>>> > >>>>> I had make this optimization: > >>>>> > >>>>> 1-Compress the index > >>>>> 2-sort the index with myisamchk > >>>>> > >>>>> > >>>>> PS.: This table is read-only, hasn't an insert, update or delete. > >>>>> > >>>>> > >>>>> > >>> ============== > >>> Atenciosamente, > >>> Jan Gomes - [EMAIL PROTECTED] > > > > > > -- > Jay Pipes > Community Relations Manager, North America, MySQL Inc. > Roaming North America, based in Columbus, Ohio > email: [EMAIL PROTECTED] mob: +1 614 406 1267 > > Are You MySQL Certified? http://www.mysql.com/certification > Got Cluster? http://www.mysql.com/cluster > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
