On 8/14/06, Dan Buettner <[EMAIL PROTECTED]> wrote:
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]
>
>
Dan
thanks for the help
so i will remove the redundant keys
below is the explain plans
but here is the weird part.
I exported the database and imported into a testDB to play with and
the query that was giving me grief in the previous message returned
immediately
Thanks
Randy
mysql> explain select * from gallery_object where dir = 'kam';
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | gallery_object | ref | dir_idx | dir_idx |
51 | const | 15 | Using where |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.01 sec)
mysql> EXPLAIN select * from gallery_object
-> where parent_id = 1
-> and dir ='dirname'
-> and obj_type = 1
-> order by order_idx;
+----+-------------+----------------+------+------------------+---------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+----------------+------+------------------+---------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | gallery_object | ref | type_idx,dir_idx |
dir_idx | 51 | const | 1 | Using where; Using filesort |
+----+-------------+----------------+------+------------------+---------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
