Table optimizing for big table. Need advice.

[Fabien Penso]

Hi.

I need a little help to optimize requests on a table, and I have no
idea how to do it (I optimized already a lot other table, but this one
is a pain). My table looks like :

CREATE TABLE comments (
id int(11) NOT NULL auto_increment,
subject varchar(80) NOT NULL default '',
timestamp timestamp(14) NOT NULL,
user_id int(6) NOT NULL default '1',
com_parent int(11) NOT NULL default '0',
res_type int(5) NOT NULL default '1',
PRIMARY KEY (id),
KEY id (id),
KEY news_id (news_id),
KEY user_id (user_id),
KEY com_parent (com_parent),
KEY res_type (res_type),
KEY timestamp (timestamp),
KEY thread_id (thread_id)
) TYPE=MyISAM PACK_KEYS=1;

It has about 500.000 entries. A simple select like :

SELECT id,subject FROM commentsnew WHERE user_id=9790 AND res_type != 3
ORDER BY id DESC LIMIT 20;

takes about 4 seconds !! :( If I run it fews times, after 3 times it
goes ok (0.03). Any idea to improve it ? I tried to go in InnoDB but it
goes as slow as on MyISAM. a 'DESC' on the request shows :

+----------+------+---------------------------+---------+---------+-------+------+----------------------------+
| comments | ref | user_id,user_id_restypeid | user_id | 4 |
const | 1602 | where used; Using filesort |
+----------+------+---------------------------+---------+---------+-------+------+----------------------------+
For information we did setup mysql with :

set-variable = key_buffer=32M
set-variable = max_allowed_packet=2M
set-variable = thread_stack=2M
set-variable = table_cache=1024
set-variable = sort_buffer=4M
set-variable = record_buffer=2M
set-variable = join_buffer=2M
set-variable = tmp_table_size=2M
set-variable = flush_time=0

[Bill Karwin]

Fabien Penso wrote:

CREATE TABLE comments (
id int(11) NOT NULL auto_increment,
subject varchar(80) NOT NULL default '',
timestamp timestamp(14) NOT NULL,
user_id int(6) NOT NULL default '1',
com_parent int(11) NOT NULL default '0',
res_type int(5) NOT NULL default '1',
PRIMARY KEY (id),
KEY id (id),
KEY news_id (news_id),
KEY user_id (user_id),
KEY com_parent (com_parent),
KEY res_type (res_type),
KEY timestamp (timestamp),
KEY thread_id (thread_id)
) TYPE=MyISAM PACK_KEYS=1;
Here's an excerpt from http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html:

"KEY is normally a synonym for INDEX. From MySQL 4.1, the key attribute
PRIMARY KEY can also be specified as just KEY when given in a column
definition. This was implemented for compatibility with other database
systems."

Is it possible that you are using 4.1, and specifying KEY is being
ignored because it's not valid to have more than one primary key per
table? I'm just speculating here, but try changing KEY to INDEX and see
if it changes anything.
SELECT id,subject FROM commentsnew WHERE user_id=9790 AND res_type != 3
ORDER BY id DESC LIMIT 20;

What happens if you ORDER BY id ASC or eliminate the LIMIT? MySQL is
supposed to have bidirectional indexes, I think, but it's worth an
experiment.

Regards,
Bill K.

[Peter Boné]

For a full indexed lookup, you might wanna try adding an index to the
table, suited for that particular query.

CREATE INDEX idxUserRes ON comments (user_id, res_type);

only run once of course :)
\sBeam
Fabien Penso wrote:

Hi.

I need a little help to optimize requests on a table, and I have no
idea how to do it (I optimized already a lot other table, but this one
is a pain). My table looks like :

CREATE TABLE comments (
id int(11) NOT NULL auto_increment,
subject varchar(80) NOT NULL default '',
timestamp timestamp(14) NOT NULL,
user_id int(6) NOT NULL default '1',
com_parent int(11) NOT NULL default '0',
res_type int(5) NOT NULL default '1',
PRIMARY KEY (id),
KEY id (id),
KEY news_id (news_id),
KEY user_id (user_id),
KEY com_parent (com_parent),
KEY res_type (res_type),
KEY timestamp (timestamp),
KEY thread_id (thread_id)
) TYPE=MyISAM PACK_KEYS=1;

It has about 500.000 entries. A simple select like :

SELECT id,subject FROM commentsnew WHERE user_id=9790 AND res_type != 3
ORDER BY id DESC LIMIT 20;

takes about 4 seconds !! :( If I run it fews times, after 3 times it
goes ok (0.03). Any idea to improve it ? I tried to go in InnoDB but it
goes as slow as on MyISAM. a 'DESC' on the request shows :

+----------+------+---------------------------+---------+---------+-------+------+----------------------------+
| comments | ref | user_id,user_id_restypeid | user_id | 4 |
const | 1602 | where used; Using filesort |
+----------+------+---------------------------+---------+---------+-------+------+----------------------------+
For information we did setup mysql with :

set-variable = key_buffer=32M
set-variable = max_allowed_packet=2M
set-variable = thread_stack=2M
set-variable = table_cache=1024
set-variable = sort_buffer=4M
set-variable = record_buffer=2M
set-variable = join_buffer=2M
set-variable = tmp_table_size=2M
set-variable = flush_time=0