Randy, it's possible your indexes are sadly out of date, or corrupted somehow.
Can you post the output of EXPLAIN select * from gallery_object where dir = 'dirname'; as well as of EXPLAIN select * from gallery_object where parent_id = 1 and dir ='dirname' and obj_type = 1 order by order_idx; Also, what does "very long time" equate to? It may not be related, but this bit of your table-create is interesting - I wonder if you have three indexes on your id column as a result: PRIMARY KEY (id), UNIQUE KEY id (id), KEY id_2 (id), PRIMARY KEY implies UNIQUE, and the id_2 index is redundant to either of the others. Dan On 8/14/06, Randy Paries <[EMAIL PROTECTED]> wrote:
Hello, I have a table (see below) that has 111599 records in it. When i do a query like select * from gallery_object where dir = 'dirname' this query takes a very long time and while the query is going on it locks the entire table so no one else can query it yet a query like select * from gallery_object where parent_id = 1 and dir ='dirname' and obj_type = 1 order by order_idx returns immediately Could someone please enlighten me Thanks CREATE TABLE gallery_object ( id int(3) unsigned NOT NULL auto_increment, obj_type int(10) unsigned default NULL, dir varchar(50) default NULL, path varchar(255) default NULL, parent_id int(10) default NULL, title varchar(100) default NULL, description varchar(255) default NULL, bgcolor varchar(15) default NULL, font varchar(50) default NULL, fontsize varchar(10) default NULL, fontcolor varchar(15) default NULL, rows_per_page int(10) unsigned default NULL, cols_per_page int(10) unsigned default NULL, image varchar(255) default NULL, sized_image varchar(255) default NULL, thumb_image varchar(255) default NULL, frame varchar(255) default NULL, order_idx int(10) unsigned default NULL, date_created timestamp(14) NOT NULL, isNested tinyint(3) unsigned default '0', PRIMARY KEY (id), UNIQUE KEY id (id), KEY id_2 (id), KEY type_idx (obj_type), KEY dir_idx (dir), KEY order_idx (order_idx) ) TYPE=MyISAM; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
