这是我的桌子：
CREATE TABLE `articles` ( `id` int(10) unsigned not null auto_increment, `author_id` int(10) unsigned not null, `date_created` datetime not null, PRIMARY KEY(id) ) ENGINE=InnoDB; CREATE TABLE `article_contents` ( `article_id` int(10) unsigned not null, `title` varchar(100) not null, `content` text not null, PRIMARY KEY(article_id) ) ENGINE=InnoDB; CREATE TABLE `article_images` ( `article_id` int(10) unsigned not null, `filename` varchar(100) not null, `date_added` datetime not null, UNIQUE INDEX(article_id, filename) ) ENGINE=InnoDB;
每篇文章可以具有一个或多个与之关联的图像。我想在页面上显示最后40篇书面文章，以及与该文章相关的最新图像。我不知道的是如何与article_images表联接，并且仅检索单个行。
编辑：解决方案必须表现良好，这一点很重要。到目前为止，我所看到的解决方案（使用派生表）需要一分钟或更长时间才能完成。
在查看了其他答案之后，它帮助我意识到了一个潜在的问题。
Articles表和article_images表都需要具有附加索引。
这是您现在拥有的：
CREATE TABLE `articles` ( `id` int(10) unsigned not null auto_increment, `author_id` int(10) unsigned not null, `date_created` datetime not null, PRIMARY KEY(id) ) ENGINE=InnoDB; CREATE TABLE `article_images` ( `article_id` int(10) unsigned not null, `filename` varchar(100) not null, `date_added` datetime not null, UNIQUE INDEX(article_id, filename) ) ENGINE=InnoDB;
这是您需要的：
CREATE TABLE `articles` ( `id` int(10) unsigned not null auto_increment, `author_id` int(10) unsigned not null, `date_created` datetime not null, PRIMARY KEY(id), INDEX (date_created DESC) ) ENGINE=InnoDB; CREATE TABLE `article_images` ( `article_id` int(10) unsigned not null, `filename` varchar(100) not null, `date_added` datetime not null, UNIQUE INDEX (article_id, filename), INDEX (article_id, date_added), ) ENGINE=InnoDB;
articles 用于按插入日期降序对文章进行排序的新索引
article_images 仍然需要第一个索引，因为它将防止将另一个具有相同文件名的图像附加到文章。 第二个索引将使查找最新图像变得简单，
有了这些新索引后，以下是将进行出价的查询：
SELECT AAA.author_id, AAA.date_created, IFNULL(BBB.title,'<NO_TITLE>') title, IFNULL(CCC.filename,'<NO-IMAGE>') filename FROM ( SELECT AA.id, AA.date_added, BB.author_id, BB.date_created FROM ( SELECT A.id,IFNULL(MAX(B.date_added),'1900-01-01 00:00:00') date_added FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A LEFT JOIN article_images B ON A.id = B.article_id GROUP BY A.id ) AA INNER JOIN articles BB USING (id) ) AAA LEFT JOIN article_contents BBB ON AAA.id=BBB.article_id LEFT JOIN article_images CCC ON (AAA.id=CCC.article_id AND AAA.date_added=CCC.date_added) ORDER BY AAA.date_created DESC;
目标是首先创建仅包含40行的内部子查询。这就是机管局应该带回来的东西。子查询AAA应该包含商品信息以及最后插入图片的日期。最后一组联接应将40个ID与标题和图像连接起来。最后一步是以降序显示结果集。
由于我没有示例数据，因此我在名为stuff的示例数据库中编写了三个存储过程
1制作桌子
DELIMITER $$ DROP PROCEDURE IF EXISTS `stuff`.`MakeTables` $$ CREATE PROCEDURE `stuff`.`MakeTables` () BEGIN DROP TABLE IF EXISTS articles; DROP TABLE IF EXISTS article_contents; DROP TABLE IF EXISTS article_images; CREATE TABLE `articles` ( `id` int(10) unsigned not null auto_increment, `author_id` int(10) unsigned not null, `date_created` datetime not null, PRIMARY KEY(id), INDEX (date_created DESC) ) ENGINE=InnoDB; CREATE TABLE `article_contents` ( `article_id` int(10) unsigned not null, `title` varchar(100) not null, `content` text not null, PRIMARY KEY(article_id)) ENGINE=InnoDB; CREATE TABLE `article_images` ( `article_id` int(10) unsigned not null, `filename` varchar(100) not null, `date_added` datetime not null, UNIQUE INDEX (article_id, filename), INDEX (article_id, date_added) ) ENGINE=InnoDB; END $$ DELIMITER ;
1制作数据
DELIMITER $$ DROP PROCEDURE IF EXISTS `stuff`.`LoadSampleData` $$ CREATE PROCEDURE `stuff`.`LoadSampleData` () BEGIN DECLARE x,y,z INT; SET x = 1; WHILE x <= 100 DO INSERT INTO articles (author_id,date_created) VALUES (RAND() * POWER(2,31), DATE('1970-01-01 00:00:00') + INTERVAL (RAND() * POWER(2,30)) SECOND); SET x = x + 1; END WHILE; SET x = 1; WHILE x <= 100 DO SET y = FLOOR(RAND() * 100); IF y >= 30 THEN INSERT INTO article_contents VALUES (x, CONCAT('TITLE_',FLOOR(RAND() * POWER(2,31))), CONCAT('CONTENT_',FLOOR(RAND() * POWER(2,31)))); END IF; SET x = x + 1; END WHILE; SET x = 1; WHILE x <= 100 DO SELECT COUNT(1) INTO y FROM article_contents WHERE article_id = x; IF y = 1 THEN SET y = 0; WHILE y < 20 DO SET y = y + 1; SET z = FLOOR(RAND() * 10); IF z >= 5 THEN INSERT INTO article_images VALUES (x, CONCAT('IMAGE_',FLOOR(RAND() * POWER(2,31))), DATE('1970-01-01 00:00:00') + INTERVAL (RAND() * POWER(2,30)) SECOND); END IF; END WHILE; END IF; SET x = x + 1; END WHILE; SELECT COUNT(1) INTO x FROM articles; SELECT COUNT(1) INTO y FROM article_contents; SELECT COUNT(1) INTO z FROM article_images; SELECT CONCAT('Articles:',x,' Titles:',y,' Images: ',z) Results; END $$ DELIMITER ;
1运行查询
DELIMITER $$ DROP PROCEDURE IF EXISTS `stuff`.`ShowLast40` $$ CREATE PROCEDURE `stuff`.`ShowLast40` () BEGIN SELECT AAA.author_id, AAA.date_created, IFNULL(BBB.title,'<NO_TITLE>') title, IFNULL(CCC.filename,'<NO-IMAGE>') filename FROM ( SELECT AA.id, AA.date_added, BB.author_id, BB.date_created FROM ( SELECT A.id,IFNULL(MAX(B.date_added),'1900-01-01 00:00:00') date_added FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A LEFT JOIN article_images B ON A.id = B.article_id GROUP BY A.id ) AA INNER JOIN articles BB USING (id) ) AAA LEFT JOIN article_contents BBB ON AAA.id=BBB.article_id LEFT JOIN article_images CCC ON (AAA.id=CCC.article_id AND AAA.date_added=CCC.date_added) ORDER BY AAA.date_created DESC; END $$ DELIMITER ;
这是在Windows桌面（MySQL 5.5.12）上运行时的示例输出：
mysql> call maketables; call loadsampledata; Query OK, 0 rows affected (0.31 sec) +------------------------------------+ | Results | +------------------------------------+ | Articles:100 Titles:67 Images: 666 | +------------------------------------+ 1 row in set (23.73 sec) mysql> call showlast40; +------------+---------------------+------------------+------------------+ | author_id | date_created | title | filename | +------------+---------------------+------------------+------------------+ | 1576679519 | 2004-01-02 14:05:17 | TITLE_1757853515 | IMAGE_1601858163 | | 992840519 | 2003-05-17 05:48:11 | TITLE_319026350 | IMAGE_1913708631 | | 1366977197 | 2003-03-20 19:37:23 | <NO_TITLE> | <NO-IMAGE> | | 1705517382 | 2003-02-07 16:48:56 | <NO_TITLE> | <NO-IMAGE> | | 1529899841 | 2002-11-02 20:59:26 | <NO_TITLE> | <NO-IMAGE> | | 1756824942 | 2002-08-28 16:01:59 | <NO_TITLE> | <NO-IMAGE> | | 175825630 | 2002-05-08 13:48:56 | TITLE_240812804 | IMAGE_1214850809 | | 757530551 | 2002-02-08 00:20:17 | TITLE_4447486 | IMAGE_1511850161 | | 840251261 | 2002-01-25 20:06:56 | TITLE_1160842143 | IMAGE_206027488 | | 964653347 | 2001-12-26 19:15:47 | TITLE_1552408257 | IMAGE_717719932 | | 2106039126 | 2001-11-11 17:09:29 | <NO_TITLE> | <NO-IMAGE> | | 2085991608 | 2001-08-01 12:48:20 | <NO_TITLE> | <NO-IMAGE> | | 1895462094 | 2000-12-02 05:31:41 | <NO_TITLE> | <NO-IMAGE> | | 1589384729 | 2000-04-28 23:55:50 | TITLE_1040850308 | IMAGE_1200414639 | | 514341550 | 2000-04-20 07:25:05 | TITLE_188288840 | IMAGE_164856430 | | 887359583 | 2000-02-13 03:30:47 | <NO_TITLE> | <NO-IMAGE> | | 1156687499 | 1999-06-16 00:29:17 | TITLE_686398996 | IMAGE_670200418 | | 1561242593 | 1998-12-08 05:50:17 | <NO_TITLE> | <NO-IMAGE> | | 1117889993 | 1998-10-23 17:02:44 | TITLE_1491217221 | IMAGE_649630126 | | 740063135 | 1998-09-16 23:52:41 | TITLE_579374776 | IMAGE_757313192 | | 429699232 | 1998-04-19 01:41:17 | TITLE_73748980 | IMAGE_1881818111 | | 1827051060 | 1998-02-27 01:01:50 | TITLE_1588619991 | IMAGE_1657322715 | | 1442984429 | 1997-11-19 21:23:35 | TITLE_184173382 | IMAGE_597809368 | | 152267158 | 1997-05-02 20:25:50 | <NO_TITLE> | <NO-IMAGE> | | 1323598169 | 1997-03-14 16:30:38 | TITLE_1355869397 | IMAGE_1058313818 | | 66122740 | 1997-01-05 15:12:20 | TITLE_1259073183 | IMAGE_198280936 | | 5161474 | 1996-06-28 10:47:26 | TITLE_1876022823 | IMAGE_1138098675 | | 1865082792 | 1996-03-01 19:09:11 | TITLE_1288151615 | IMAGE_245974646 | | 1923481146 | 1995-08-07 00:36:11 | TITLE_922744000 | IMAGE_2067090321 | | 1725218958 | 1995-03-18 05:15:29 | TITLE_583120586 | IMAGE_592773824 | | 117806248 | 1995-01-05 02:34:32 | <NO_TITLE> | <NO-IMAGE> | | 1428777335 | 1993-06-06 01:52:32 | TITLE_661148588 | IMAGE_633345518 | | 1091245943 | 1993-06-05 05:51:47 | TITLE_1407444563 | IMAGE_538936256 | | 2088382260 | 1993-03-25 06:03:29 | TITLE_1144364681 | IMAGE_1790013089 | | 625878569 | 1992-12-21 07:41:26 | TITLE_1319355723 | IMAGE_921580624 | | 110555110 | 1992-01-01 20:49:59 | <NO_TITLE> | <NO-IMAGE> | | 1110532475 | 1991-11-20 07:19:32 | <NO_TITLE> | <NO-IMAGE> | | 1726795146 | 1990-10-09 00:23:44 | TITLE_782624350 | IMAGE_1760322575 | | 370183888 | 1990-03-30 15:59:17 | <NO_TITLE> | <NO-IMAGE> | | 1497483317 | 1990-02-19 01:25:41 | TITLE_776483948 | IMAGE_665824222 | +------------+---------------------+------------------+------------------+ 40 rows in set (0.00 sec)
试试看 ！！！
更新
我绝对确定您正在阅读的40个日期时间实际上是前40个。我运行查询：从ORDER BY date_created DESC文章中选择*；确保;确定。
更新2011-05-17 14:06
mysql> call maketables; call loadsampledata; Query OK, 0 rows affected (0.45 sec) +-------------------------------------+ | Results | +-------------------------------------+ | Articles:100 Titles:67 Images: 6739 | +-------------------------------------+ 1 row in set (3 min 45.45 sec) Query OK, 0 rows affected (3 min 45.45 sec) mysql> call showlast40; +------------+---------------------+------------------+------------------+ | author_id | date_created | title | filename | +------------+---------------------+------------------+------------------+ | 196582776 | 2004-01-05 14:09:04 | <NO_TITLE> | <NO-IMAGE> | | 1880371016 | 2003-07-31 05:50:37 | TITLE_1191518827 | IMAGE_1562208019 | | 22204986 | 2003-02-16 14:09:22 | <NO_TITLE> | <NO-IMAGE> | | 355490160 | 2002-11-21 02:35:19 | <NO_TITLE> | <NO-IMAGE> | | 869510149 | 2001-12-27 22:07:52 | TITLE_1528616779 | IMAGE_223327284 | | 2063556512 | 2001-04-16 18:47:46 | TITLE_1839975091 | IMAGE_1282187005 | | 529754190 | 2000-07-14 19:44:01 | TITLE_1557423205 | IMAGE_1931606737 | | 166226262 | 1999-11-08 03:27:22 | <NO_TITLE> | <NO-IMAGE> | | 1981417562 | 1999-09-11 12:59:10 | TITLE_198262896 | IMAGE_1491273871 | | 831057001 | 1999-06-14 15:06:31 | TITLE_1170272131 | IMAGE_760396200 | | 1454252623 | 1998-06-02 08:35:46 | <NO_TITLE> | <NO-IMAGE> | | 1435450777 | 1997-11-17 18:10:34 | TITLE_482497458 | IMAGE_1331932705 | | 1536315541 | 1997-11-02 05:24:49 | <NO_TITLE> | <NO-IMAGE> | | 2078028530 | 1997-03-14 22:36:58 | TITLE_321332010 | IMAGE_1897983295 | | 701651581 | 1997-01-13 22:36:58 | TITLE_1337390701 | IMAGE_1630983859 | | 101442444 | 1996-11-22 09:40:16 | <NO_TITLE> | <NO-IMAGE> | | 51114930 | 1996-11-20 03:24:49 | TITLE_1866751135 | IMAGE_1669595407 | | 722056183 | 1996-08-03 15:23:01 | <NO_TITLE> | <NO-IMAGE> | | 1178720989 | 1996-06-29 22:47:19 | TITLE_579734376 | IMAGE_833229222 | | 511355958 | 1996-03-10 09:32:46 | TITLE_1540275289 | IMAGE_1168117261 | | 831921829 | 1996-01-31 06:36:04 | TITLE_661038882 | IMAGE_1199197195 | | 1288455163 | 1995-08-22 00:34:25 | TITLE_1599332515 | IMAGE_822445764 | | 1976208956 | 1995-06-02 09:23:01 | TITLE_58372998 | IMAGE_793318650 | | 2092066982 | 1995-03-28 20:35:37 | TITLE_1693142377 | IMAGE_1176935479 | | 1083841549 | 1994-07-11 18:46:52 | TITLE_1955674591 | IMAGE_1240485919 | | 359037132 | 1994-07-11 02:44:19 | TITLE_713454936 | IMAGE_1072569732 | | 1471985773 | 1994-05-10 17:08:01 | TITLE_1065017724 | IMAGE_393097704 | | 1888864730 | 1994-01-15 17:41:28 | TITLE_1060275498 | IMAGE_230810100 | | 1688028488 | 1993-12-28 06:36:58 | <NO_TITLE> | <NO-IMAGE> | | 1739777948 | 1993-02-15 00:30:31 | TITLE_1226842225 | IMAGE_1615058467 | | 445721334 | 1991-12-15 20:54:49 | TITLE_1336145587 | IMAGE_2114729323 | | 1661002442 | 1991-06-30 05:49:34 | TITLE_151142910 | IMAGE_1623325381 | | 2092223006 | 1991-06-13 13:15:58 | TITLE_33175860 | IMAGE_1225117771 | | 1553434585 | 1991-01-12 03:34:25 | TITLE_728483442 | IMAGE_1954153339 | | 528544608 | 1990-11-10 08:21:04 | <NO_TITLE> | <NO-IMAGE> | | 1043927395 | 1990-10-05 00:48:49 | TITLE_304307448 | IMAGE_1702062493 | | 1685702960 | 1990-04-28 05:44:19 | TITLE_1909853341 | IMAGE_263553036 | | 1392428383 | 1990-03-07 15:08:46 | <NO_TITLE> | <NO-IMAGE> | | 643714153 | 1990-02-14 08:32:10 | TITLE_837416724 | IMAGE_1673964259 | | 2132028206 | 1989-09-28 16:04:07 | TITLE_614908878 | IMAGE_1362210487 | +------------+---------------------+------------------+------------------+ 40 rows in set (0.01 sec)
一切都可以像我以前发布的那样工作，甚至可以使用更大的图像集。这是来自ShowLast40存储过程的查询：
更新2011-05-17 15:16
只是为了笑，我增加了样本并重新运行它。查询速度相同。
mysql> call maketables; call loadsampledata; call showlast40; Query OK, 0 rows affected (0.38 sec) +----------------------------------------+ | Results | +----------------------------------------+ | Articles:1000 Titles:886 Images: 88596 | +----------------------------------------+ 1 row in set (51 min 22.29 sec) Query OK, 0 rows affected (51 min 22.29 sec) +------------+---------------------+------------------+------------------+ | author_id | date_created | title | filename | +------------+---------------------+------------------+------------------+ | 1464539515 | 2004-01-07 22:45:04 | TITLE_676010724 | IMAGE_1877060293 | | 272558724 | 2004-01-03 23:44:19 | TITLE_1213504045 | IMAGE_550812606 | | 2000476448 | 2003-12-28 18:05:10 | TITLE_1762951489 | IMAGE_1201290847 | | 955209697 | 2003-12-25 00:55:43 | TITLE_1064749344 | IMAGE_1335865507 | | 1657429856 | 2003-12-19 01:03:13 | TITLE_1931852743 | IMAGE_905288424 | | 759381001 | 2003-11-12 10:46:52 | TITLE_878255772 | IMAGE_2014780795 | | 1269478951 | 2003-11-06 02:06:22 | TITLE_2026098781 | IMAGE_982272966 | | 1049672131 | 2003-10-04 20:55:34 | TITLE_2043080215 | IMAGE_987859662 | | 1429108729 | 2003-09-16 19:07:52 | TITLE_424483080 | IMAGE_35379150 | | 1672198676 | 2003-09-13 11:49:52 | TITLE_1131552745 | IMAGE_875049630 | | 1645878842 | 2003-08-24 13:42:04 | TITLE_1077302833 | IMAGE_702269538 | | 172347180 | 2003-08-21 14:26:37 | TITLE_558691044 | IMAGE_1091183587 | | 1137674509 | 2003-08-15 08:44:37 | TITLE_1982979709 | IMAGE_1234487941 | | 282998112 | 2003-08-05 10:01:34 | TITLE_353831568 | IMAGE_738487608 | | 246145344 | 2003-08-02 00:42:31 | TITLE_376954044 | IMAGE_1279375459 | | 218409162 | 2003-07-14 02:55:16 | TITLE_1932540991 | IMAGE_1078689211 | | 593263087 | 2003-07-12 22:47:01 | TITLE_1604012533 | IMAGE_834822870 | | 2115914174 | 2003-07-06 03:06:31 | TITLE_1268165545 | IMAGE_1068632322 | | 552557275 | 2003-07-01 16:45:22 | TITLE_2022112717 | IMAGE_1410588295 | | 1500437041 | 2003-06-29 20:05:19 | TITLE_35559258 | IMAGE_159953586 | | 1098371257 | 2003-06-09 07:29:37 | TITLE_1694076415 | IMAGE_1409619391 | | 1570373503 | 2003-05-22 16:45:04 | TITLE_125157894 | IMAGE_723393492 | | 1330507411 | 2003-05-05 21:40:07 | TITLE_1571250589 | IMAGE_701840418 | | 1666035620 | 2003-04-26 02:51:40 | TITLE_504713706 | IMAGE_1410357553 | | 1458179791 | 2003-04-19 05:34:25 | <NO_TITLE> | <NO-IMAGE> | | 1365758305 | 2003-03-28 10:09:58 | TITLE_704554170 | IMAGE_2085080137 | | 2131082774 | 2003-03-26 16:43:25 | TITLE_1411034929 | IMAGE_303539208 | | 103396632 | 2003-02-14 09:11:28 | TITLE_915927396 | IMAGE_1381045723 | | 396479202 | 2003-02-01 15:51:40 | <NO_TITLE> | <NO-IMAGE> | | 2019916250 | 2003-01-30 00:44:46 | <NO_TITLE> | <NO-IMAGE> | | 431091906 | 2003-01-29 13:08:37 | <NO_TITLE> | <NO-IMAGE> | | 705166549 | 2003-01-23 21:37:07 | TITLE_1530318643 | IMAGE_257673696 | | 1278327049 | 2002-12-31 16:39:40 | TITLE_772845324 | IMAGE_1355754913 | | 1871174528 | 2002-12-30 19:03:40 | TITLE_65725764 | IMAGE_522904938 | | 611892727 | 2002-12-22 10:19:07 | TITLE_333758274 | IMAGE_734815032 | | 758497849 | 2002-12-04 15:05:10 | TITLE_129140574 | IMAGE_244407066 | | 518111034 | 2002-10-17 16:38:10 | TITLE_1976498683 | IMAGE_2008599775 | | 1737725786 | 2002-10-15 23:52:52 | TITLE_1441053871 | IMAGE_1595265847 | | 1206055789 | 2002-10-03 22:07:52 | TITLE_562697952 | IMAGE_198940092 | | 702790153 | 2002-08-31 17:37:16 | TITLE_1788304903 | IMAGE_1212944101 | +------------+---------------------+------------------+------------------+ 40 rows in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)
更新2011-05-17 15:40
这是新的输出，包括最新图像的日期：
mysql> call showlast40; +------------+---------------------+------------------+------------------+---------------------+ | author_id | date_created | title | filename | image_date | +------------+---------------------+------------------+------------------+---------------------+ | 1464539515 | 2004-01-07 22:45:04 | TITLE_676010724 | IMAGE_1877060293 | 2003-10-16 02:06:58 | | 272558724 | 2004-01-03 23:44:19 | TITLE_1213504045 | IMAGE_550812606 | 2003-12-28 07:25:43 | | 2000476448 | 2003-12-28 18:05:10 | TITLE_1762951489 | IMAGE_1201290847 | 2003-08-31 16:56:01 | | 955209697 | 2003-12-25 00:55:43 | TITLE_1064749344 | IMAGE_1335865507 | 2003-11-11 18:37:07 | | 1657429856 | 2003-12-19 01:03:13 | TITLE_1931852743 | IMAGE_905288424 | 2003-09-26 07:20:01 | | 759381001 | 2003-11-12 10:46:52 | TITLE_878255772 | IMAGE_2014780795 | 2003-09-30 06:54:40 | | 1269478951 | 2003-11-06 02:06:22 | TITLE_2026098781 | IMAGE_982272966 | 2003-07-28 11:59:10 | | 1049672131 | 2003-10-04 20:55:34 | TITLE_2043080215 | IMAGE_987859662 | 2003-11-19 05:26:37 | | 1429108729 | 2003-09-16 19:07:52 | TITLE_424483080 | IMAGE_35379150 | 2003-11-18 22:17:55 | | 1672198676 | 2003-09-13 11:49:52 | TITLE_1131552745 | IMAGE_875049630 | 2003-06-08 00:42:58 | | 1645878842 | 2003-08-24 13:42:04 | TITLE_1077302833 | IMAGE_702269538 | 2002-04-02 01:21:49 | | 172347180 | 2003-08-21 14:26:37 | TITLE_558691044 | IMAGE_1091183587 | 2003-08-13 08:30:22 | | 1137674509 | 2003-08-15 08:44:37 | TITLE_1982979709 | IMAGE_1234487941 | 2003-12-17 11:53:28 | | 282998112 | 2003-08-05 10:01:34 | TITLE_353831568 | IMAGE_738487608 | 2003-11-08 22:03:22 | | 246145344 | 2003-08-02 00:42:31 | TITLE_376954044 | IMAGE_1279375459 | 2003-12-05 02:30:49 | | 218409162 | 2003-07-14 02:55:16 | TITLE_1932540991 | IMAGE_1078689211 | 2003-07-14 15:59:37 | | 593263087 | 2003-07-12 22:47:01 | TITLE_1604012533 | IMAGE_834822870 | 2003-09-02 05:48:22 | | 2115914174 | 2003-07-06 03:06:31 | TITLE_1268165545 | IMAGE_1068632322 | 2003-04-28 16:29:01 | | 552557275 | 2003-07-01 16:45:22 | TITLE_2022112717 | IMAGE_1410588295 | 2003-11-01 01:55:16 | | 1500437041 | 2003-06-29 20:05:19 | TITLE_35559258 | IMAGE_159953586 | 2003-08-02 10:34:07 | | 1098371257 | 2003-06-09 07:29:37 | TITLE_1694076415 | IMAGE_1409619391 | 2004-01-07 01:00:13 | | 1570373503 | 2003-05-22 16:45:04 | TITLE_125157894 | IMAGE_723393492 | 2003-09-26 23:22:43 | | 1330507411 | 2003-05-05 21:40:07 | TITLE_1571250589 | IMAGE_701840418 | 2003-11-19 20:57:31 | | 1666035620 | 2003-04-26 02:51:40 | TITLE_504713706 | IMAGE_1410357553 | 2003-11-18 01:30:04 | | 1458179791 | 2003-04-19 05:34:25 | <NO_TITLE> | <NO-IMAGE> | <NO-IMAGE-DATE> | | 1365758305 | 2003-03-28 10:09:58 | TITLE_704554170 | IMAGE_2085080137 | 2003-11-11 16:35:19 | | 2131082774 | 2003-03-26 16:43:25 | TITLE_1411034929 | IMAGE_303539208 | 2003-05-14 12:59:37 | | 103396632 | 2003-02-14 09:11:28 | TITLE_915927396 | IMAGE_1381045723 | 2003-12-28 18:26:28 | | 396479202 | 2003-02-01 15:51:40 | <NO_TITLE> | <NO-IMAGE> | <NO-IMAGE-DATE> | | 2019916250 | 2003-01-30 00:44:46 | <NO_TITLE> | <NO-IMAGE> | <NO-IMAGE-DATE> | | 431091906 | 2003-01-29 13:08:37 | <NO_TITLE> | <NO-IMAGE> | <NO-IMAGE-DATE> | | 705166549 | 2003-01-23 21:37:07 | TITLE_1530318643 | IMAGE_257673696 | 2003-08-23 19:06:22 | | 1278327049 | 2002-12-31 16:39:40 | TITLE_772845324 | IMAGE_1355754913 | 2003-12-22 16:40:25 | | 1871174528 | 2002-12-30 19:03:40 | TITLE_65725764 | IMAGE_522904938 | 2003-09-06 07:08:01 | | 611892727 | 2002-12-22 10:19:07 | TITLE_333758274 | IMAGE_734815032 | 2003-09-22 19:16:43 | | 758497849 | 2002-12-04 15:05:10 | TITLE_129140574 | IMAGE_244407066 | 2003-07-15 12:38:37 | | 518111034 | 2002-10-17 16:38:10 | TITLE_1976498683 | IMAGE_2008599775 | 2004-01-06 16:37:34 | | 1737725786 | 2002-10-15 23:52:52 | TITLE_1441053871 | IMAGE_1595265847 | 2003-11-24 15:23:10 | | 1206055789 | 2002-10-03 22:07:52 | TITLE_562697952 | IMAGE_198940092 | 2003-08-23 11:56:46 | | 702790153 | 2002-08-31 17:37:16 | TITLE_1788304903 | IMAGE_1212944101 | 2003-12-19 22:56:01 | +------------+---------------------+------------------+------------------+---------------------+ 40 rows in set (0.01 sec)
这是新的查询
SELECT AAA.author_id, AAA.date_created, IFNULL(BBB.title,'<NO_TITLE>') title, IFNULL(CCC.filename,'<NO-IMAGE>') filename, IFNULL(CCC.date_added,'<NO-IMAGE-DATE>') image_date FROM ( SELECT AA.id, AA.date_added, BB.author_id, BB.date_created FROM ( SELECT A.id,IFNULL(MAX(B.date_added),'1900-01-01 00:00:00') date_added FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A LEFT JOIN article_images B ON A.id = B.article_id GROUP BY A.id ) AA INNER JOIN articles BB USING (id) ) AAA LEFT JOIN article_contents BBB ON AAA.id=BBB.article_id LEFT JOIN article_images CCC ON (AAA.id=CCC.article_id AND AAA.date_added=CCC.date_added) ORDER BY AAA.date_created DESC;
我的查询会做一件事，其他人的查询不会做：
我的查询在加入任何其他表之前先获取最后40篇文章。
其他人的查询将首先连接所有内容，然后尝试在大型临时表上执行LIMIT 40。
我的查询的运行时间不是数据量的因素。
无论我创建的样本大小如何，我的查询都必须是最快的！
