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

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

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

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

Ответ 1

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

В таблице статей и в таблице 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,'<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 идентификаторов с заголовком и изображением. Последний шаг - представить результирующий набор в порядке спуска.

Поскольку у меня нет выборочных данных, я написал три хранимые процедуры в примерной базе данных с именем 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. Я выполнил запрос: 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 | <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 на большой временной таблице.

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

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

Ответ 2

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

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, сохраненный в таблице статей. Выполнение позволит вам сразу присоединиться к изображениям.

Ответ 3

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

В 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

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

Ответ 4

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

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

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

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

2nd Решение. Удалите текстовый столбец из 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 и время, затраченное на выполнение запроса.

Ответ 5

Что-то вроде этой работы?

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

Ответ 6

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

Ответ 7

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

Принудительная скорость вставки для значительно улучшенной функции поиска.

Ответ 8

Этот должен быть довольно эффективным, поскольку он не использует производные таблицы.

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;

GROUP_CONCAT возвращает строковый список всех имен файлов в группе, упорядоченных по статье article_images_date_added DESC и разделенных символом "\t".
SUBSTRING_INDEX вырезает строку перед первым "\t", поэтому будет возвращено только самое последнее имя файла.

(Очевидно, что вы также должны иметь соответствующие индексы, добавленные в article_images.article_id, article_images.date_created и articles.date_created.)

Ответ 9

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

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

Вам также нужно добавить индекс в таблицу article_images

(article_id ASC, date_added ASC)

Ответ 10

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

использование выше query.it будет работать для вас

Ответ 11

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

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);

Иначе, если нет необходимости иметь изображение, связанное с каждой статьей (что более логично), это должно работать:

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))