Alright, I have a table
CREATE TABLE IF NOT EXISTS `messages` (
`id` int(10) unsigned NOT NULL auto_increment,
`mailbox` enum('INBOX','OUTBOX','SAVED') default NULL,
`msg-id` int(11) unsigned NOT NULL,
`from-player-id` int(11) unsigned NOT NULL,
`from-player-name` varchar(60) NOT NULL,
`to-player-id` int(10) unsigned NOT NULL,
`to-player-name` varchar(60) NOT NULL,
`username` varchar(60) NOT NULL,
`received` timestamp NULL default NULL,
`message` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `msg-id` (`msg-id`),
KEY `to-player-id` (`to-player-id`),
KEY `to-player-name` (`to-player-name`),
KEY `from-player-id` (`from-player-id`),
KEY `from-player-name` (`from-player-name`),
KEY `username` (`username`),
FULLTEXT KEY `message` (`message`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `messages` (`id`, `mailbox`, `msg-id`, `from-player-id`, `from-player-name`, `to-player-id`, `to-player-name`, `username`, `received`, `message`) VALUES
(1, 'SAVED', 101914998, 1487928, 'Ultibot', 2193258, 'McUlti', 'McUlti', '2011-05-07 09:07:58', 'You there?'),
(2, 'SAVED', 101914999, 2193258, 'McUlti', 1487928, 'Ultibot', 'McUlti', '2011-05-07 09:08:48', 'Yeah, I''m here. How''s it going?');
and the data in the table is populated with a bot written in PHP. My bot attempts to stay logged into a site and keep an updated log of all messages sent to it so it has access to all my PMs and it can reply to the ones it detects as new etc.
1)The bot first checks its mailbox on an interval, maybe once every minute - so he can reply instantly to people which message him.
2)It gets the message id of all the messages in my mailbox. For example it may detect the following four PMs in my mailbox: 101914998, 101914999, 101915005, 101915006. (most of the time these will be old messages)
3)Then PHP would genereate the following query:
SELECT t.a FROM (SELECT 101914998 AS `a` UNION SELECT 101914999 UNION SELECT 101915005 UNION SELECT 101915006) AS t LEFT JOIN `messages` m ON m.`msg-id`=t.a WHERE m.`msg-id` IS NULL
which results in:
meaning two of the PMs in my mailbox are new and need to be added to the database.
4)PHP would then add the new messages (101915005 and 101915006) to the database and respond to the people that sent them. For example "Hi, How's it going?" would result in a reply "All systems appears to be functioning normally. And you?"
Where you come in
My Join-Union-Subquery query works fine, but I suspect there is a better way to acheive my results since my UNION usage seems a bit unorthodox then JOINing it onto a subquery just seems desperate... I want to rewrite my query. Is there a better way to do it that you can think of in my situation? I fear the query might get slow when dealing with 100+ maybe even 1000+ PMs at once, when my mailbox gets that big - although I haven't tested my query yet on over 100 UNIONs at once. Remember this query is going to be execute very frequently -- every minute -- and I don't want my bot to encounter unnecessary problems if my query can be written to be faster etc.
p.s. my server's MySQL version is 5.0.51a.