StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>After looking over the other answers, it helped me realize an underlying problem.</p> <p>The articles table and article_images table both need to have an additional index.</p> <p>This is what you have now:</p> <pre><code>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; </code></pre> <p>This is what you need:</p> <pre><code>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; </code></pre> <p>articles<BR> The new index for ordering the articles by insertion date in descending order</p> <p>article_images<BR> The first index is still needed since it will guard against attaching the another image with the same filename to an article.<BR> The second index will make finding the most recent image simple,</p> <p>With those new indexes in place, here is the query that will do your bidding:</p> <pre><code> 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; </code></pre> <p>The goal is first to create an internal subquery that only has 40 rows. That is what AA should bring back. Subquery AAA should have the article info plus the date the last image was inserted. The last set of joins should connect the 40 ids with a title and an image. The final step is to present the result set in descensding order.</p> <p>Since I do not have sample data, I wrote three stored procedures in a sample database called stuff</p> <p>1 to make the tables</p> <pre><code>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 ; </code></pre> <p>1 to make the data</p> <pre><code>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 ; </code></pre> <p>1 to run the query</p> <pre><code>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 ; </code></pre> <p>Here is an example output when I ran it on my Windows Desktop (MySQL 5.5.12):</p> <pre><code>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) </code></pre> <p>Give it a Try !!!</p> <p>UPDATE</p> <p>I made absolutely sure that the 40 datetimes you are reading are in fact the top 40. They are. I ran the query : select * from articles ORDER BY date_created DESC; to make sure.</p> <p>UPDATE 2011-05-17 14:06</p> <pre><code>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) </code></pre> <p>Everything works as I published before even with a larger set of images. Here is the query from the ShowLast40 Stored Procedure:</p> <pre><code> 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; </code></pre> <p>UPDATE 2011-05-17 15:16</p> <p>Just for laughs, I increased the sample and reran it. Same query speed.</p> <pre><code>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) </code></pre> <p>UPDATE 2011-05-17 15:40</p> <p>Here is the new output including the date of the latest image:</p> <pre><code>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) </code></pre> <p>Here is the new query</p> <pre><code> 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; </code></pre> <p>My query does one thing nobody else's query does:</p> <p>My query gets the last 40 articles first before joining to any other table.</p> <p>Everybody else's queries joins everything first and then attempts to do LIMIT 40 on a large temp table.</p> <p>My query's running time is not a factor of the amount of data.</p> <p>Regardless of the sample size I create, my query has to be the fastest !!!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload