php - MySQL 查询优化 - 分组值

Question

好的，这是我的数据库结构:

TABLE `books`
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
  `title` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
  `author` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
  PRIMARY KEY (`id`)
  UNIQUE KEY `title` (`title`)

TABLE `chapters`
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
  `book_id` smallint(5) unsigned NOT NULL DEFAULT '0'
  `number` smallint(5) unsigned NOT NULL DEFAULT '0'
  `title` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
  PRIMARY KEY (`id`)
  KEY `book_id` (`book_id`) -> Foreign on `books`.`id`

TABLE `pages`
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
  `book_id` smallint(5) unsigned NOT NULL DEFAULT '0'
  `chapter_id` smallint(5) unsigned NOT NULL DEFAULT '0'
  `number` smallint(5) unsigned NOT NULL DEFAULT '0' // real number, for page 1 of book 1 this has value 1, for page 1 of book 2 this has value 1...
  `value` float NOT NULL DEFAULT '0'
  PRIMARY KEY (`id`)
  KEY `book_id` (`book_id`) -> Foreign on `books`.`id`
  KEY `chapter_id` (`chapter_id`)  -> Foreign on `chapters`.`id`

现在...在 PHP 页面上，我想显示每本书的平均 pages.value，每本书每一章的平均 pages.value以及每个页面的单个 pages.value:

Book 1 AVG = X
Book 1 Chapter   1            AVG = X
Book 1 Chapter ...            AVG = X
Book 1 Chapter   N            AVG = X
Book 1 Chapter   1 Page   1   VAL = X
Book 1 Chapter   1 Page ...   VAL = X
Book 1 Chapter   1 Page   N   VAL = X

And so on...

当然，使用 3 个查询和一些连接会非常简单，我可以使用 PHP 以编程方式重新格式化数据以满足我的需要。但我想知道是否可以将这些查询(顺便说一句，执行起来非常慢)优化为一个查询，或者可以通过使用高级语法来减少处理时间。

Answer 1

为您希望用于搜索的列编制索引会大有帮助。例如:

CREATE INDEX index_name ON table_name(column_name);

完成同样事情的另一种方法是:

ALTER TABLE table_name ADD INDEX ( column_name );

这样你的查询执行时间会很快...