Alternative to NOT IN clause?

I want to create a single query to select all items, which exclude certain items with NOT IN clause

however these items need to exclude is pretty much (~20 records) but table has ~100k records.

SELECT m.m_id,m.m_username,t.topic_id,t.topic_title,t.topic_img,t.topic_img_thumb,t.topic_type,t.topic_reply,t.topic_lastpost_time,c.fc_id,fc_pid,fg_id,fc_name FROM p_topics t
INNER JOIN frcat c ON t.fc_id = c.fc_id
INNER JOIN members m ON t.m_id=m.m_id
WHERE t.topic_state=‘1’ AND t.topic_locked=‘0’ AND t.topic_id NOT IN (82559,82564,82580,82512,82558,82586,82595,82597,82639,82589,82646,82635,82642,82634,82637)
ORDER BY t.topic_date_create DESC LIMIT 0,20;

and this thing creates the slow query :frowning:

so how can i use the other way to do this thing with better performance?

what causes the slow query is likely a missing index

can you do an EXPLAIN on the query please

yeb, here’s my exlain with this query result

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c ALL PRIMARY NULL NULL NULL 57 Using temporary; Using filesort
1 SIMPLE t ref PRIMARY,fc_id,m_id fc_id 4 site.c.fc_id 1423 Using where
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 site.t.m_id 1

while i’m not an expert at performance optimization, i would say that your ORDER BY is causing the slowness

try it without and see what happens

but i can do this query without ORDER BY :frowning:
because i need the latest record. how can i do this without use ORDER BY or NOT IN clause?

Remove the ORDER BY clause. Does that speed up the query substantially? Remember you’re trouble shooting here, not trying to come up with your final query.

The other obvious thing to verify: does removing the NOT IN clause improve the performance significantly?

While adjusting the indexes and/or query is the correct solution, it might be easier to just implement the NOT IN functionality using whatever your processing language is (i.e. PHP or whatever).

hey, so, how to check the table or field is missing index? :-?

do a SHOW CREATE TABLE for the table, and it will show you all the indexes on the table

no, i mean that how to know what INDEX field i missed / what field supposed to be index?
i INDEXED many fields , but i think i missed some fields

Why did you choose to index the fields you did? Why did you choose to leave others out?

Indexes help in the performance of selects and negatively impact inserts and updates. You need to weigh this when adding indexes.

You can also have indexes across multiple columns, sometimes those are advantageous.

Please post the list of indexes currently in use for the 3 tables

yeb, here’s my list of indexes currently in use:

p_topics:

PRIMARY BTREE Yes No topic_id 84846 A
Edit Drop fc_id BTREE No No fc_id 59 A
Edit Drop m_id BTREE No No m_id 4040 A

And frcat

Action Keyname Type Unique Packed Field Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No fc_id 57 A
Edit Drop fc_pid BTREE No No fc_pid 0 A
Edit Drop fg_id BTREE No No fg_id 0 A
Edit Drop fc_state BTREE No No fc_state 0 A

members:

Edit Drop PRIMARY BTREE Yes No m_id 27335 A

please do a SHOW CREATE TABLE for each table, and it will show you all the indexes on the table


SHOW CREATE TABLE p_topics;
SHOW CREATE TABLE frcat;
SHOW CREATE TABLE members;

i dont know why, because i listed my indexes above :expressionless:

here’s my SHOW CREATE TABLE queries

p_topics


CREATE TABLE `p_topics` (
   `topic_id` int(11) NOT NULL auto_increment,
  `fc_id` int(11) NOT NULL,
  `m_id` int(11) NOT NULL,
  `m_username` varchar(50) collate utf8_unicode_ci default NULL,
  `topic_type` tinyint(4) NOT NULL default '0' COMMENT '0-Normal; 1-Sticky; 2-Anocement; 3-Global',
  `topic_icon` smallint(2) NOT NULL default '0',
  `topic_title` varchar(255) collate utf8_unicode_ci NOT NULL,
  `topic_date_create` int(11) NOT NULL,
  `topic_lastpost_time` int(11) NOT NULL,
  `topic_date_close` int(11) NOT NULL default '0',
  `topic_img` text collate utf8_unicode_ci,
  `topic_img_thumb` enum('0','1','2') collate utf8_unicode_ci NOT NULL default '0',
  `topic_flash` enum('0','1') collate utf8_unicode_ci default NULL,
  `topic_tag` varchar(255) collate utf8_unicode_ci default NULL,
  `topic_view` int(11) NOT NULL default '0',
  `topic_reply` int(11) NOT NULL default '0',
  `topic_fav` int(11) NOT NULL default '0',
  `topic_state` tinyint(4) NOT NULL default '1',
  `topic_locked` tinyint(4) NOT NULL default '0' COMMENT '1: closed, 0: open',
  `topic_src` varchar(255) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`topic_id`),
  KEY `fc_id` (`fc_id`),
  KEY `m_id` (`m_id`),
  FULLTEXT KEY `topic_title` (`topic_title`),
  FULLTEXT KEY `topic_tag` (`topic_tag`)
) ENGINE=MyISAM AUTO_INCREMENT=85473 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and frcat


CREATE TABLE `frcat` (
  `fc_id` smallint(4) NOT NULL auto_increment,
  `fc_pid` smallint(4) NOT NULL default '0',
  `icon_filename` varchar(32) collate utf8_unicode_ci default NULL,
  `fg_id` smallint(6) NOT NULL,
  `fc_name` varchar(255) collate utf8_unicode_ci NOT NULL,
  `fc_tag` varchar(255) collate utf8_unicode_ci default NULL,
  `fc_state` tinyint(1) NOT NULL default '1' COMMENT '0: hidden - 1: active',
  `fc_order` smallint(6) NOT NULL default '1',
  `fc_posts` int(11) NOT NULL default '0',
  `fc_topics` int(11) NOT NULL default '0',
  `display_on_parent` tinyint(4) NOT NULL default '0' COMMENT '1: Display on parent forum description; 0: Not display',
  PRIMARY KEY  (`fc_id`),
  KEY `fc_pid` (`fc_pid`),
  KEY `fg_id` (`fg_id`),
  KEY `fc_state` (`fc_state`)
) ENGINE=MyISAM AUTO_INCREMENT=58 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and members


CREATE TABLE `members` (
  `m_id` int(11) NOT NULL auto_increment,
  `m_avatar` varchar(255) default NULL,
  `m_username` varchar(100) NOT NULL,
  `m_fullname` varchar(64) default NULL,
  `m_gender` enum('0','1','2') NOT NULL default '0',
  `m_email` varchar(100) NOT NULL,
  `m_password` varchar(100) NOT NULL,
  `m_birthday` char(10) default NULL,
  `m_birthyear` smallint(4) default NULL,
  `m_location` varchar(64) default NULL,
  `m_relationship_status` tinyint(1) NOT NULL default '0',
  `m_ym_id` varchar(64) default NULL,
  `m_joindate` int(11) NOT NULL,
  `m_mood` tinyint(2) default '0',
  `m_coin` int(11) NOT NULL default '0',
  `m_level` tinyint(1) NOT NULL default '1',
  `m_state` enum('-1','0','1') NOT NULL default '0',
  `m_lastlogin` int(11) NOT NULL,
  `m_lastactivity` int(11) NOT NULL default '0',
  PRIMARY KEY  (`m_id`),
  KEY `m_gender` (`m_gender`),
  FULLTEXT KEY `m_username` (`m_username`),
  FULLTEXT KEY `m_fullname` (`m_fullname`)
) ENGINE=MyISAM AUTO_INCREMENT=52644 DEFAULT CHARSET=utf8

hope you can help?

looks okay to me, but i am not a performance expert

how to know what INDEX field i missed / what field supposed to be index?
i INDEXED many fields , but i think i missed some fields

so, do you know what field supposed to be index?
should i index topic_date_create field (unix timestamp)