Be seen. Boost your question’s priority for more expert views and faster solutions
abl: has a message from staff3
swa: has a message from staff3
dtw: has NO messages from staff3
fre: has NO messages from staff3
gam: has NO messages from staff3
h1a: has a message from staff3
nyh: has NO messages from staff3
big: has a message from staff3
CREATE TABLE `a_messages2` (
`a_messages_id` INT (11) auto_increment primary key
, `profile_id` VARCHAR(20)
, `sender` VARCHAR(20)
, `message_id` BIGINT (20)
, `this_user` VARCHAR(20)
);
INSERT INTO a_messages2
(`profile_id`, `sender`, `message_id`, `this_user`)
VALUES
('abl', 'staff3', 3662615602, 'staff3'),
('abl', 'abl', 3660687080, 'staff3'),
('abl', 'staff3', 3658803944, 'staff3'),
('abl', 'staff3', 3649952930, 'staff3'),
('abl', 'staff3', 3644250249, 'staff3'),
('abl', 'staff3', 3614235515, 'staff3'),
('abl', 'abl', 3614140442, 'staff3'),
('abl', 'staff3', 3613984751, 'staff3'),
('abl', 'staff3', 3613936332, 'staff3'),
('abl', 'abl', 3613469574, 'staff3'),
('abl', 'staff3', 3612970481, 'staff3'),
('abl', 'abl', 3609276901, 'staff3'),
('abl', 'abl', 3609276896, 'staff3'),
('abl', 'staff3', 3866086756, 'staff3'),
('abl', 'staff3', 3866079064, 'staff3'),
('abl', 'abl', 3856964132, 'staff3'),
('abl', 'staff3', 3844779912, 'staff3'),
('abl', 'staff3', 3916948647, 'staff3'),
('abl', 'staff3', 3880367425, 'staff3'),
('abl', 'abl', 3977088222, 'staff3'),
('abl', 'staff3', 3974513295, 'staff3'),
('abl', 'staff3', 3973285282, 'staff3'),
('abl', 'abl', 3960907806, 'staff3'),
('abl', 'staff3', 4003664468, 'staff3'),
('abl', 'abl', 4002246444, 'staff3'),
('abl', 'staff3', 3996089944, 'staff3'),
('abl', 'staff3', 4024726432, 'staff3'),
('abl', 'abl', 4021516823, 'staff3'),
('abl', 'staff3', 4018957318, 'staff3'),
('abl', 'abl', 4050609080, 'staff3'),
('abl', 'staff3', 4052368867, 'staff3'),
('abl', 'abl', 4052424820, 'staff3'),
('abl', 'staff3', 4053183569, 'staff3'),
('swa','swa','4153199171','staff3'),
('swa','staff3','4150323049','staff3'),
('swa','swa','4146362776','staff3'),
('swa','staff3','4142884979','staff3'),
('swa','staff3','4142884976','staff3'),
('DTw', 'DTw', '3658976250', 'staff3'),
('DTw', 'DTw', '3658422687', 'staff3'),
('DTw', 'DTw', '3653399588', 'staff3'),
('DTw', 'DTw', '3653174764', 'staff3'),
('DTw', 'DTw', '3546098525', 'staff3'),
('DTw', 'DTw', '3545571594', 'staff3'),
('DTw', 'DTw', '3545571593', 'staff3'),
('DTw', 'DTw', '3540574071', 'staff3'),
('DTw', 'DTw', '3540454344', 'staff3'),
('DTw', 'DTw', '3540376883', 'staff3'),
('fre', 'fre', '3544889093', 'staff3'),
('gam', 'gam', '3955910171', 'staff3'),
('gam', 'gam', '3955910165', 'staff3'),
('gam', 'gam', '3649458031', 'staff3'),
('gam', 'gam', '3648816297', 'staff3'),
('gam', 'gam', '3648783076', 'staff3'),
('gam', 'gam', '3648516109', 'staff3'),
('gam', 'gam', '3647758123', 'staff3'),
('gam', 'gam', '3554774961', 'staff3'),
('gam', 'gam', '3540425395', 'staff3'),
('gam', 'gam', '3540357159', 'staff3'),
('gam', 'gam', '3540345288', 'staff3'),
('h1a', 'staff3', '3700557963', 'staff3'),
('h1a', 'h1a', '3693946789', 'staff3'),
('h1a', 'h1a', '3693806133', 'staff3'),
('h1a', 'staff3', '3684800156', 'staff3'),
('h1a', 'h1a', '3684173904', 'staff3'),
('h1a', 'h1a', '3682095987', 'staff3'),
('h1a', 'h1a', '3682095983', 'staff3'),
('h1a', 'h1a', '3542388954', 'staff3'),
('nyh', 'nyh', '3554817690', 'staff3'),
('nyh', 'nyh', '3554796334', 'staff3'),
('nyh', 'nyh', '3554701760', 'staff3'),
('nyh', 'nyh', '3554689575', 'staff3'),
('nyh', 'nyh', '3541014726', 'staff3'),
('nyh', 'nyh', '3541012663', 'staff3'),
('nyh', 'nyh', '3540619436', 'staff3'),
('nyh', 'nyh', '3540474531', 'staff3'),
('big', 'big', '3947506235', 'staff3'),
('big', 'staff3', '3947496223', 'staff3'),
('big', 'big', '3947489941', 'staff3'),
('big', 'staff3', '3938366539', 'staff3'),
('big', 'staff3', '3647107151', 'staff3'),
('big', 'big', '3587867444', 'staff3'),
('big', 'staff3', '3542393433', 'staff3')
$mysqli->query("ALTER TABLE `a_messages2` ADD INDEX ( `profile_id` )" );
add index to all columnsThis is an ambiguous question. Let me try to explain why.
so are you saying I can just add index to every column and this will reduce select times
CREATE TABLE `a_messages2` (
`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_and_sender (profile_id,sender)
);
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
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"
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 suggestingyes there is a unique key
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 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
)
What is the point for this query?I think the boss needs this for reporting
(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)
);
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 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
)
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.