Получение одной строки из таблицы объединения

Вот мои таблицы:

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 и получить только одну строку.

Изменить: важно, чтобы решение работало хорошо. Решения, которые я видел до сих пор, которые используют производные таблицы, занимают минуту или больше.

Просмотрев другие ответы, это помогло мне понять основную проблему.

Таблица статей и статья_изображения должны иметь дополнительный индекс.

Это то, что у вас есть сейчас:

 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; 

статьи
Новый индекс для заказа статей по дате ввода в порядке убывания

article_images
Первый индекс по-прежнему необходим, поскольку он будет защищать от прикрепления другого изображения с тем же именем файла к статье.
Второй индекс упростит поиск самого последнего изображения,

С этими новыми индексами, вот запрос, который будет делать ваши ставки:

  SELECT AAA.author_id, AAA.date_created, IFNULL(BBB.title,'') title, IFNULL(CCC.filename,'') 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 идентификаторов с заголовком и изображением. Последний шаг – представить результирующий набор в порядке спуска.

Поскольку у меня нет выборочных данных, я написал три хранимые процедуры в примерной базе данных под названием 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,'') title, IFNULL(CCC.filename,'') 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 |  |  | | 1705517382 | 2003-02-07 16:48:56 |  |  | | 1529899841 | 2002-11-02 20:59:26 |  |  | | 1756824942 | 2002-08-28 16:01:59 |  |  | | 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 |  |  | | 2085991608 | 2001-08-01 12:48:20 |  |  | | 1895462094 | 2000-12-02 05:31:41 |  |  | | 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 |  |  | | 1156687499 | 1999-06-16 00:29:17 | TITLE_686398996 | IMAGE_670200418 | | 1561242593 | 1998-12-08 05:50:17 |  |  | | 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 |  |  | | 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 |  |  | | 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 |  |  | | 1110532475 | 1991-11-20 07:19:32 |  |  | | 1726795146 | 1990-10-09 00:23:44 | TITLE_782624350 | IMAGE_1760322575 | | 370183888 | 1990-03-30 15:59:17 |  |  | | 1497483317 | 1990-02-19 01:25:41 | TITLE_776483948 | IMAGE_665824222 | +------------+---------------------+------------------+------------------+ 40 rows in set (0.00 sec) 

Попробуйте !!!

ОБНОВИТЬ

Я абсолютно уверен, что 40 пользователей, которые вы читаете, фактически являются топ-40. Я выполнил запрос: select * from articles 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 |  |  | | 1880371016 | 2003-07-31 05:50:37 | TITLE_1191518827 | IMAGE_1562208019 | | 22204986 | 2003-02-16 14:09:22 |  |  | | 355490160 | 2002-11-21 02:35:19 |  |  | | 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 |  |  | | 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 |  |  | | 1435450777 | 1997-11-17 18:10:34 | TITLE_482497458 | IMAGE_1331932705 | | 1536315541 | 1997-11-02 05:24:49 |  |  | | 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 |  |  | | 51114930 | 1996-11-20 03:24:49 | TITLE_1866751135 | IMAGE_1669595407 | | 722056183 | 1996-08-03 15:23:01 |  |  | | 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 |  |  | | 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 |  |  | | 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 |  |  | | 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,'') title, IFNULL(CCC.filename,'') 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 |  |  | | 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 |  |  | | 2019916250 | 2003-01-30 00:44:46 |  |  | | 431091906 | 2003-01-29 13:08:37 |  |  | | 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 |  |  |  | | 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 |  |  |  | | 2019916250 | 2003-01-30 00:44:46 |  |  |  | | 431091906 | 2003-01-29 13:08:37 |  |  |  | | 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,'') title, IFNULL(CCC.filename,'') filename, IFNULL(CCC.date_added,'') 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 на большой временной таблице.

Время выполнения моего запроса не является фактором количества данных.

Независимо от размера выборки, которую я создаю, мой запрос должен быть самым быстрым !!!

Это случай, когда встроенный подзапрос, а не соединение, будет работать хорошо:

 select articles.*, article_contents.title, article_contents.content, (select article_images.filename from article_images where article_images.article_id = articles.id order by article_images.date_added desc limit 1 ) as image_filename from articles join article_contents on article_contents.article_id = articles.id order by articles.date_created desc limit 40; 

Производительность мудрая, она будет nestloop через верхние 40 рядов статей, который является самым быстрым возможным планом; и для каждой из этих строк подзапрос будет nestloop в верхней применимой строке в article_images, что также является самым быстрым планом для данной статьи.

Если вам нужно извлечь больше одного поля из таблицы изображений, я полагаю, у вас есть image_id. Предположим, возьмите вместо этого image_id, а затем сделайте второй запрос с предложением in, чтобы получить нужные строки.

Альтернативным (и немного более быстрым) подходом будет использование триггеров, чтобы сохранить последнюю image_id, сохраненную в таблице статей. Выполнение позволит вам напрямую присоединиться к изображениям.

В основном вы хотите сделать строки, содержащие групповой максимум поля , которое было связано с ʞɔıu в MySQL – Control, какая строка возвращается группой .

В MySQL невозможно выполнить ORDER BY до GROUP BY. Вы можете делать Subselects, как описано в первом связанном ответе, или как показано в группе MySQL. Заказывая .

В вашем случае это, скорее всего, приведет к плохой производительности, поэтому можно будет использовать два отдельных запроса. Не совсем элегантный, но надежный:

 SELECT a.id FROM articles a ORDER BY a.date_created DESC LIMIT 40 

и для каждой из 40 строк результата с% ARTICLE_ID% является текущим идентификатором строки:

 SELECT ai.filename FROM article_images ai WHERE ai.article_id = %ARTICLE_ID% ORDER BY ai.date_added DESC LIMIT 1 

Мой совет будет другим: избыточность. Сохраните последний образ статьи избыточно в таблице статей. Это приводит к простым и быстрым запросам.

Позвольте мне сначала описать причину времени, затраченного на запрос для решения этой проблемы. Мой английский не очень хорош, но я постараюсь объяснить его наилучшим образом.

Давайте возьмем пример соединения 2 таблицы. Когда мы используем команду join table, что в основном делается. Согласно запросу, движок сканирует первую таблицу, затем переходит ко второй таблице и начинает считывать первую таблицу и вторую таблицу и где выполняется совпадение запроса, она берет память. Теперь давайте рассмотрим пример объединения трех таблиц, прочитайте 1-й стол, затем 2 секунды, а для выполнения на 3-м sql, если данные тяжелые, затем создайте таблицу tmp и начните чтение, а затем выполните команду и, если она обычно сохраняется в памяти.

Теперь ваша проблема заключается не в команде объединения таблиц, а в проблеме только структура 2-й таблицы. Он имеет текстовый столбец, и это минимальное использование, и во время поиска движок должен выбрать этот запрос и выполнить функцию чтения и записи на нем, а при каждой записи или чтении использование памяти уменьшается, а двигатель становится медленным, медленным и медленным.

Решение. Для решения этой проблемы есть два решения. Первое решение – сделать раздел второй таблицы горизонтально и вертикально. То, что произойдет с его движком, сделает отметку во время поиска, и большой fragment данных будет написан быстро, и результат синхронизации возрастет. Но я не рекомендую решение, так как это только уменьшит время, но время чтения и письма не уменьшится. Что бы я предложил второе Решение.

2-е решение. Удалите текстовый столбец из 2-й таблицы и сделайте 4-ю таблицу author_aritcle_content двумя столбцами заголовка и содержимого и присоединитесь к четвертой таблице в последнем. Что будет с этим решением, первый сканер двигателя сканирует 1-ю таблицу с 10 + 10 байтами, и когда мы перейдем ко второй таблице, сканирование будет выполнено в 10 + 10 + 10 + 100, а с третьей таблицей будет 10 + 10 + 10 + 100 + 10 + 100, и в этот момент движок должен искать только 240 байт, и ваш результат готов, и просто движок должен добавить содержимое, и это будет сделано легко. Сокращение времени в запросе очень велико, поскольку он не должен принимать текстовый столбец с движком, поскольку он будет присоединяться последним, только с результатами, которые будут отображаться.

Сегодня я добавил 10 000 статей в таблицу и выполнил приведенный ниже запрос, и он занял 0.2015 сек. Каждая статья имеет байты 65 000, а имя изображения – 10 байт, а запрос:

 select a.id, a.author_id, a.date_created, b.title, c.filename, c.date_added, d.content from articles as a, article_contents as b, article_images as c, article_contents as d where a.id = b.article_id and b.article_id = c.article_id and c.article_id = d.article_id order by a.date_created desc limit 0, 40; 

Я снова опубликую результаты, когда номера статей пройдут до 50 000 и время, затраченное на выполнение запроса.

Будет что-то вроде этой работы?

 select * from articles left join (select article_id, filename from article_images group by article_id order by date_added desc) on id = article_id 
 SELECT a.id, ai.filename FROM articles a LEFT JOIN (SELECT article_id, MAX(date_added) AS MaxDate FROM article_images GROUP BY article_id) maxai INNER JOIN article_images ai ON maxai.article_id = ai.article_id AND maxai.MaxDate = ai.date_added ON a.id = maxai.article_id ORDER BY a.date_created DESC LIMIT 40 

If performance is major concern when searching and displaying results then I propose you alter your table structure so that the last image ID is stored in the article contents table. That way retrieval speed is optimal.

Sacrifice insert speed for a far improved search function.

Попробуйте что-то вроде этого

 SELECT A.author_id , Images.article_id , Images.filename , Images.date_added FROM articles AS A LEFT JOIN ( SELECT AI1.article_id , AI1.filename , AI1.date_added FROM article_images AS AI1 WHERE AI1.date_added = ( SELECT MAX(date_added) FROM article_images AS AI2 WHERE AI2.article_id = AI1.article_id ) ) AS Images ON A.id = Images.article_id 

You will also need to add an index to the article_images table

 (article_id ASC, date_added ASC) 
 SELECT a.id,ac.title,ac.content,ai.filename FROM articles a,article_contents ac,article_images ai WHERE a.id=ac.article_id and a.id=ai.article_id ORDER BY a.date_created desc LIMIT 40 

use above query.it will works for you

This one should be pretty efficient as it does not use any derived tables.

 SELECT articles.id, articles.author_id, articles.date_created, SUBSTRING_INDEX(GROUP_CONCAT(filename ORDER BY article_images.date_added DESC SEPARATOR "\t"),"\t",1) as latestFilename FROM articles JOIN article_images ON articles.id = article_images.article_id GROUP BY articles.id, articles.author_id, articles.date_created ORDER BY articles.date_created DESC LIMIT 40; 

The GROUP_CONCAT returns a string list of all filenames in the group, ordered by article_images_date_added DESC and separated by a “\t”.
The SUBSTRING_INDEX will cut the string before the first “\t” so only the latest filename will be returned.

(Obviously, you should also have the relevant indexes added on article_images.article_id, article_images.date_created and articles.date_created.)

Try this if you are sure that each article has an image

 SELECT a.author_id,a.date_created,i.filename FROM test.articles a left join article_images i on i.article_id=a.id where i.date_added =(select Max(date_added) from article_images i2); 

Else if is not necessary to have an image related to each article (which is more logical) this should work:

 SELECT a.author_id,a.date_created,i.filename FROM test.articles a left join article_images i on (i.article_id=a.id and i.date_added =(select Max(date_added) from article_images i2)) 
  • ОШИБКА 1698 (28000): доступ запрещен для пользователя 'root' @ 'localhost'
  • Включить заголовки при использовании SELECT INTO OUTFILE?
  • Сумма MYSQL () для разных строк
  • Как удалить дубликаты в таблице MySQL?
  • Как установить значение по умолчанию для столбца MySQL Datetime?
  • Тихая установка MySQL неверна для исполнения
  • Индекс MySQL Integer vs DateTime
  • Как подключить Android-приложение к базе данных MySQL?
  • Получение данных для графика гистограммы
  • Могу ли я решить эту проблему с помощью чистого mysql? (присоединение к ';' разделенные значения в столбце)
  • MySQL хранимой процедуры против функции, которую я буду использовать, когда?
  • Interesting Posts

    Почему я не могу назначить лямбду объекту?

    Запрос HTTP-клиента Java с заданным таймаутом

    Окно Alt-Tab Windows 7 исчезает, когда включен aero peek

    Как работает RegexOptions.Compiled?

    Измените первый символ в каждом слове строки на верхний регистр.

    Что делает «дружественный URL»?

    Перемещение / перемещение вкладки в сочетании с ScrollView?

    Как я основываю правило перспективы на число адресов «Кому:»?

    Установленная оперативная память: 4 ГБ, но 2.96 ГБ. Полезно … почему?

    Как сбросить Windows 7 до шрифта по умолчанию для всей системы?

    «Переопределить метод суперclassа» Ошибки после импорта проекта в Eclipse

    Учитывая строку, сгенерируйте регулярное выражение, которое может анализировать * похожие * строки

    Странный шрифт в Firefox

    Замена NA с последним значением, отличным от NA

    Сериализация / десериализация пользовательской коллекции с дополнительными свойствами с помощью Json.Net

    Давайте будем гением компьютера.