create table statement with indexes

$mysqli->query("ALTER TABLE `a_messages2` ADD INDEX ( `profile_id` )" );

Select all Open in new window

add index to all columns

explain
                      SELECT
                      profile_id,this_user
                      FROM a_messages2
                      WHERE (
                               ( /* here we determine who originated the profile */
                                   message_id IN (SELECT min(message_id)
                                                  FROM a_messages2 GROUP BY profile_id)
                                AND
                                   profile_id <> sender /* originated by staff */
                               )
                            OR
                               ( /* here we determine who has sent latest message */
                                   message_id IN (SELECT max(message_id)
                                                  FROM a_messages2 GROUP BY profile_id)
                                AND
                                   profile_id = sender /* needs a response by staff */
                               )
                            )
                      GROUP BY profile_id
                      HAVING count(*) = 2

Select all Open in new window

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,a_messages2,ALL,NULL,NULL,NULL,NULL,9707,"Using where; Using temporary; Using filesort"
3,"DEPENDENT SUBQUERY",a_messages2,ALL,NULL,NULL,NULL,NULL,9707,"Using temporary; Using filesort"
2,"DEPENDENT SUBQUERY",a_messages2,ALL,NULL,NULL,NULL,NULL,9707,"Using temporary; Using filesort"

Select all Open in new window

you're hiding some information : there is a primary key and it is actually used. i'd assume that it is similar to the compound index i was suggesting

CREATE TABLE `a_messages2` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` varchar(20) default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9969 DEFAULT CHARSET=utf8;

Select all Open in new window

select profile_id,this_user
FROM
a_messages2
WHERE
profile_id = sender /* needs a response by staff */
and message_id IN
      (
      SELECT
      max(message_id)
      FROM
      a_messages2
      WHERE
      profile_id IN
            (
            SELECT
            profile_id
            FROM a_messages2
            WHERE
            message_id IN (SELECT min(message_id) FROM a_messages2 GROUP BY profile_id)
            AND profile_id <> sender /* originated by staff */
            )
      GROUP BY profile_id
      )

Select all Open in new window

What is the point for this query?

(profile_id,message_id) will likely be much more usefull as mysql will be able to retrieve the min message_ids for each profile_id very fast, while it currently requires a full table scan, a temporary table and a sort

CREATE TABLE `a_messages3` (
       `a_messages_id` INT (11) auto_increment  primary key
     , `profile_id` VARCHAR(20)
     , `sender` VARCHAR(20)
     , `message_id` BIGINT (20)
     , `this_user` VARCHAR(20)
     , PRIMARY KEY messages_id
     , INDEX profile_id (profile_id)
     , INDEX profile_id_and_message_id (profile_id,message_id)
     );

Select all Open in new window

CREATE TABLE `a_messages3` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  KEY `profile_id` (`profile_id`),
  KEY `profile_id_and_message_id` (`profile_id`,`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9708 DEFAULT CHARSET=utf8;

Select all Open in new window

select profile_id,this_user
FROM
a_messages3
WHERE
profile_id = sender /* needs a response by staff */
and message_id IN
      (
      SELECT
      max(message_id)
      FROM
      a_messages3
      WHERE
      profile_id IN
            (
            SELECT
            profile_id
            FROM a_messages3
            WHERE
            message_id IN (SELECT min(message_id) FROM a_messages3 GROUP BY profile_id)
            AND profile_id <> sender /* originated by staff */
            )
      GROUP BY profile_id
      )

Select all Open in new window