rgb192
asked on
messages for profiles that I wrote first AND are waiting for a response.
I want to see all the messages for profiles that I wrote first AND are waiting for a response.
I want to see all the messages (messages staff3 sent, messages 'big' sent) for profile_id 'big'.
this a query to see all the messages where the highest message_id is not sent by staff3
I am looking for the all the messages from a profile where lowest message_id='staff3' and highest message_id!='staff3'
From the mysql inserts, the only answer is profile_id 'big'
I want to see all the messages (messages staff3 sent, messages 'big' sent) for profile_id 'big'.
abl: abl wrote first, staff3 wrote last
swa: swa wrote first, swa wrote last
dtw: dtw wrote first, dtw wrote last
gam: gam wrote first, gam wrote last
h1a: hla wrote first, staff3 wrote last
nyh: nyh wrote first, nyh wrote last
big: staff3 wrote first, big wrote last
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')
this a query to see all the messages where the highest message_id is not sent by staff3
SELECT
m2.profile_id
, m2.sender
, m2.message_id
, m2.this_user
FROM (
SELECT
mx.sender
FROM (
SELECT
profile_id
, sender
, max(message_id) AS message_id
FROM a_messages2
WHERE profile_id=sender
GROUP BY
profile_id
, sender
) AS mx
LEFT JOIN a_messages2 AS m ON mx.profile_id = m.profile_id
AND mx.sender <> m.sender
AND mx.message_id < m.message_id
WHERE m.a_messages_id IS NULL
) AS mmx
INNER JOIN a_messages2 AS m2 ON mmx.sender = m2.sender
;
I am looking for the all the messages from a profile where lowest message_id='staff3' and highest message_id!='staff3'
From the mysql inserts, the only answer is profile_id 'big'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Why don't you save yourself a lot of processing and store origin of profile?
I other questions you have differentiated between:
> first contact by staff
> first contact by non-staff
Thank you for the suggestion, but that would be a larger php problem which may never have an answer.
using mysql workbench I am trying to run the whole query but query execution interrupted after 3 minutes
https://www.experts-exchange.com/questions/28296665/increase-query-timeout-time-using-mysql-workbench.html
or I have tried only running the query in the middle without the larger query which also timeout
I also tried adding where 'staff3'
SELECT
profile_id
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 */
)
)
and this_user='staff3'
GROUP BY profile_id
HAVING count(*) = 2
There are numerous staff members
still does a timeout
even when I do another staff member that has 20 messages
Efficiency is precisely why I suggested storing that decision. As your store of messages grows you will face an ever increasing issue determining who started what profile - each and every time you run these queries. Your data model lacks a date/time attributes as well.
How many records are currently in a_messages2 and what is the growth rate?
How many records are currently in a_messages2 and what is the growth rate?
ASKER
Question has been answered, Thank you .
when I do
create like
and
insert into where this_user='staff3'
subquery works
with a smaller user, the entire query works
I will open up another question about future problems.
when I do
create like
and
insert into where this_user='staff3'
subquery works
with a smaller user, the entire query works
I will open up another question about future problems.
Thanks.
When you open that new question I suggest you include all details of the table(s) involved
including indexes
Cheers, Paul
When you open that new question I suggest you include all details of the table(s) involved
including indexes
Cheers, Paul
ASKER
Would indexes be the primary key auto increment column. How could i do insert with indexes
By the nature of that question it appears you have much to catch-up on.
here's an introduction:
http://en.wikipedia.org/wiki/Database_index
The purpose of indexing is to speed-up queries.
You can have more than one index on any table. You can still do inserts into a table with more than one index.
However there is a "trade-off":
Indexes speed-up queries but can slow-down inserts/updates
The reason for slowing-down inserts is that each additional index is also maintained while inserting/updating. However, I think you would find that with your table additional indexes would be of substantial benefit.
here's an introduction:
http://en.wikipedia.org/wiki/Database_index
The purpose of indexing is to speed-up queries.
You can have more than one index on any table. You can still do inserts into a table with more than one index.
However there is a "trade-off":
Indexes speed-up queries but can slow-down inserts/updates
The reason for slowing-down inserts is that each additional index is also maintained while inserting/updating. However, I think you would find that with your table additional indexes would be of substantial benefit.
ASKER
Indexing may speed up queries for me.
But the previous query you showed me failed (for me) because of the mysql_workbench query timeout.
I have two related question of which I think you can solve
indexing table:
https://www.experts-exchange.com/questions/28318003/create-table-statement-with-indexes.html
another query that returns profile_id where staff has never solved a message:
https://www.experts-exchange.com/questions/28318002/messages-from-profile-id-that-have-no-messages-sent-from-staff.html
But the previous query you showed me failed (for me) because of the mysql_workbench query timeout.
I have two related question of which I think you can solve
indexing table:
https://www.experts-exchange.com/questions/28318003/create-table-statement-with-indexes.html
another query that returns profile_id where staff has never solved a message:
https://www.experts-exchange.com/questions/28318002/messages-from-profile-id-that-have-no-messages-sent-from-staff.html
Why don't you save yourself a lot of processing and store origin of profile?
I other questions you have differentiated between:
> first contact by staff
> first contact by non-staff
store this distinction