Improving this slow SELECT query

Databases
1

Hi there :slight_smile:

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:


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 :slight_smile:

2

please do a SHOW CREATE TABLE for each table so that we can see the indexes

3

first thing we need to do is get the single query performing well

you need to add this index –

ALTER TABLE members_contacts
ADD INDEX(contact_member_id,member_id)

now try this –


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

4

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!

5

that works, but it isn’t a single query

do you still want a single query?

6

Hi Rudy,

Here is the SHOW CREATE TABLE for members_contacts


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


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:


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 :slight_smile:

7

Hi,

Better late than never in my reply :blush:

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 :slight_smile:

8

you’re welcome and thanks for the update :slight_smile: