在查看了其他答案之后，它帮助我意识到了一个潜在的问题。
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` ()BEGINDROP 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存储过程的查询：
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;
更新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。
我的查询的运行时间不是数据量的因素。
无论我创建的样本大小如何，我的查询都必须是最快的！
