SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Improving this slow SELECT query

    Hi there

    I was hoping for some help with a SELECT query which is running a bit too slow. I have a table called members_contacts which is used to create a relationship between members. The table looks like this:

    member_id (PK)
    contact_member_id (PK)
    date_added

    member_id is the ID of the member that requested the connection
    contact_member_id is the ID of the member that received the connection request.

    Now that the table has grown the queries are becoming too slow. To get a list of members contacts I am doing something like this:
    Code:
    SELECT
    	m.firstname,
    	m.lastname
    FROM
    	members AS m
    	members_contacts AS mc
    WHERE
    (
    	mc.contact_member_id = $memberid
    	AND
    	m.member_id = mc.member_id
    )
    OR
    (
    	mc.member_id = $memberid
    	AND
    	m.member_id = mc.contact_member_id
    )
    There are usually other joins too, but this is the part that slows down a number of different queries.

    Is there an better way of doing this?

    Many thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for each table so that we can see the indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Here is the SHOW CREATE TABLE for members_contacts
    Code:
    CREATE TABLE `members_contacts` (
     `member_id` int(10) unsigned NOT NULL default '0',
     `contact_member_id` int(10) unsigned NOT NULL default '0',
     `rel_a` tinyint(1) unsigned NOT NULL default '0',
     `rel_b` tinyint(1) unsigned NOT NULL default '0',
     `rel_c` tinyint(1) unsigned NOT NULL default '0',
     `rel_d` tinyint(1) unsigned NOT NULL default '0',
     `rel_e` tinyint(1) unsigned NOT NULL default '0',
     `rel_f` tinyint(1) unsigned NOT NULL default '0',
     `rel_g` tinyint(1) unsigned NOT NULL default '0',
     `approved` tinyint(1) unsigned NOT NULL default '0',
     `date_added` datetime NOT NULL default '0000-00-00 00:00:00',
     PRIMARY KEY  (`member_id`,`contact_member_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    The rel_ columns are just various flags to tell me more about the relationship between the two members.

    Here is the members table but I've removed some of the general info columns
    Code:
    CREATE TABLE `members` (
     `member_id` int(10) unsigned NOT NULL auto_increment,
     `firstname` varchar(32) NOT NULL default '',
     `lastname` varchar(32) NOT NULL default '',
     `email` varchar(64) NOT NULL default '',
     `password` varchar(32) NOT NULL default '',
     `is_active` tinyint(1) unsigned NOT NULL default '0',
     `rpx_id` varchar(255) NOT NULL default '',
     `rpx_twitter_id` int(11) NOT NULL,
     `rpx_facebook_id` int(11) NOT NULL,
     `is_suspended` tinyint(1) unsigned NOT NULL default '0',
     PRIMARY KEY  (`member_id`),
     KEY `rpxid_i` (`rpx_id`),
     KEY `rpxtwitterid_i` (`rpx_twitter_id`),
     KEY `rpxfacebookid_i` (`rpx_facebook_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=15504 DEFAULT CHARSET=latin1
    Typically, I often display a list of members that belong to a certain 'group' and for each member displayed I need to show the connection status between that member and the logged in member. i.e. one of the following values will be shown against each member displayed: 'connected', 'not connected', 'awaiting approval'.

    In the interim I've resorted to getting a list of contact IDs with their approval status using a separate query like this:
    Code:
    SELECT
    	member_id,
    	contact_member_id,
    	approved
    FROM
    	members_contacts
    WHERE
    	member_id = $memberid
    	OR
    	contact_member_id = $memberid
    ... and then I using PHP code to create an array of contact IDs with approval status. Then for each member displayed I check if that member's ID is withn the array of contacts and display the relevant connection status. That method currently works quicker than doing it all in 1 query.

    I hope that makes sense.

    Many thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that works, but it isn't a single query

    do you still want a single query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Yes, a single query option would be preferable please. I can't see how I can do a more efficiently query without having to add additional PHP code to strip out any junk data like I do with the separate query.

    So ideally I need a query for selecting details from the members table for all member contacts. Also a single query selecting a list of members along with the 'approved' status from the member_contacts table so we can see if each member is a contact, not a contact or not approved for the logged in member.

    Thanks again!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    first thing we need to do is get the single query performing well

    you need to add this index --
    Code:
    ALTER TABLE members_contacts
    ADD INDEX(contact_member_id,member_id)
    now try this --
    Code:
    SELECT m.firstname
         , m.lastname
      FROM members_contacts AS mc
    INNER
      JOIN members AS m
        ON m.member_id = mc.member_id
     WHERE mc.contact_member_id = $memberid
    UNION ALL
    SELECT m.firstname
         , m.lastname
      FROM members_contacts AS mc
    INNER
      JOIN members AS m
        ON m.member_id = mc.member_id
     WHERE mc.member_id = $memberid
    let me know if you don't understand what's going on with this query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Better late than never in my reply

    I've finally got round to untilising this properly in a somewhat bigger query than the trimmed down example above. It definitely works better and is easier to maintain, so a belated thank you

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you're welcome and thanks for the update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •