SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what causes the slow query is likely a missing index

    can you do an EXPLAIN on the query please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but i can do this query without ORDER BY
    because i need the latest record. how can i do this without use ORDER BY or NOT IN clause?

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    692
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    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).

  8. #8
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey, so, how to check the table or field is missing index? :-?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by phpguru View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SitePoint Enthusiast erangalp's Avatar
    Join Date
    Oct 2010
    Location
    Israel
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please post the list of indexes currently in use for the 3 tables

  13. #13
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by erangalp View Post
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for each table, and it will show you all the indexes on the table
    Code:
    SHOW CREATE TABLE p_topics;
    SHOW CREATE TABLE frcat;
    SHOW CREATE TABLE members;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i dont know why, because i listed my indexes above :|

    here's my SHOW CREATE TABLE queries

    p_topics
    Code MySQL:
    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

    Code MySQL:
    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

    Code MySQL:
    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?
    Last edited by ScallioXTX; Oct 27, 2010 at 02:34. Reason: Replaced \n with actual enters and [quote] with [highlight="MySQL"]

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    looks okay to me, but i am not a performance expert
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot phpguru's Avatar
    Join Date
    Sep 2005
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •