Fast de-normalized View in MySQL, how?

Let's say, I have a normal normalized database like this:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'test'),
(2, 'user');

_

CREATE TABLE `groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
INSERT INTO `groups` (`id`, `name`) VALUES
(1, 'test');

_

CREATE TABLE `Data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user` int(10) unsigned NOT NULL,
  `group` int(10) unsigned NOT NULL,
  `time` int(10) unsigned NOT NULL,
  `data` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`user`),
  KEY `group` (`group`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

INSERT INTO `Data` (`id`, `user`, `group`, `time`, `data`) VALUES
(1, 2, 1, 1301861998, 'something'),
(2, 1, 1, 1301862045, 'something else');

ALTER TABLE `Data`
  ADD CONSTRAINT `Data_ibfk_2` FOREIGN KEY (`group`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Data_ibfk_1` FOREIGN KEY (`user`) REFERENCES `users` (`开发者_JAVA百科id`) ON DELETE CASCADE ON UPDATE CASCADE;

Now I want to create a view, that shows this database de-normalized for easier manual browsing. How do I properly create this view? What I have so far is:

CREATE VIEW `data_view` AS
  select `Data`.`id`,
 (select `users`.`name` AS `name` from `users` where (`users`.`id` = `Data`.`user`)) AS `user`,
 (select `groups`.`name` AS `name` from `groups` where (`groups`.`id` = `Data`.`group`)) AS `group`,
 from_unixtime(`Data`.`time`) AS `time`,
 `Data`.`data` AS `data` from `Data`;

It works, but because of the two inner SELECT statements it is awfull awfull slow. (for a DB with ~2 Million rows it needs more than a minute instead of less then a second for the same query on the data table. My guess is, that the select statements to get the friendly names for user and group are executed for each row and not cached. How can I optimize this?

---

CREATE VIEW `data_view` AS
  select `Data`.`id`,
  `users`.`name` as `user`,
  `groups`.`name` as `group`,
 from_unixtime(`Data`.`time`) AS `time`,
 `Data`.`data` AS `data` from `Data`
 INNER JOIN `users` ON (`users`.`id` = `Data`.`user`)
 INNER JOIN `groups` where (`groups`.`id` = `Data`.`group`)

try this. and make sure, that users.id, groups.id, data.user and data.group are indexes..

inner selects in mysql are much slower than joins.

---

Is there any reason you aren't using joins? It looks like your query can be rewritten as follows:

SELECT d.id, u.name, g.name as GroupName, from_unixtime(d.time) as time
from Data as d
INNER JOIN users as u
ON d.user = u.id
INNER JOIN groups as g
ON d.group = g.id

I would then look at what indexes you have in place. If your performance can be improved by adding an index you may want to do it, but keep in mind that having indexes speeds up selects and slows down inserts/updates/deletes so if data is updated frequently you will have to see if it's worth it.

If you were using SQL Server I would recommend creating an Indexed view, but in MySQL this is not an option.

---

Views in MySQL generally suck really badly, because the optimiser can't effectively optimise them.

But if you rewrite that as a join (as others suggested) and don't make it more complicated, it should be fine.