Hello!
There is a chat, respectively, there are two tables users and messages . By the value of autoincrement, it can be seen that there are more than 800,000 messages, more than 11,000 users (the uniqueness of users is determined by the composite username-ip index).
CREATE TABLE `messages` ( `id` mediumint(7) unsigned NOT NULL auto_increment, `userid` mediumint(6) unsigned NOT NULL, `msg` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=897235 ; CREATE TABLE `users` ( `id` mediumint(6) unsigned NOT NULL auto_increment, `username` varchar(255) NOT NULL, `ip` int unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username_ip` (`username`,`ip`), KEY `usernames` (`username`), KEY `ips` (`ip`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=11956 ; Already the head swells, tell me how to optimize the following request, one problem should be only one request, in order not to change the script logic further (briefly: you need to select all the user's messages by his IP and it is natural to bring them nicknames and addresses to them):
SELECT `t2`.`id` as `Number`, inet_ntoa(`t1`.`ip`) as `IP`, `t1`.`username` as `Nick`, `t2`.`msg` as `Message` FROM `messages` as `t2` LEFT JOIN `users` as `t1` ON `t2`.`userid` = `t1`.`id` WHERE `t1`.`ip` = inet_aton('11.11.11.11') ORDER BY `t2`.`id` Accordingly, limiting the request data does not help, I can’t think of a different request structure, whether the brain doesn’t cook, or the knowledge is lost. But I feel that somewhere I missed something.
