Jan Gomes wrote:
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]
