Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)

Question

I am fairly new to MariaDB and I am struggling with one issue that I cannot get to the bottom of it. This is the query:

SELECT SQL_NO_CACHE STRAIGHT_JOIN
    `c`.`Name` AS `CategoryName`, 
    `c`.`UrlSlug` AS `CategorySlug`, 
    `n`.`Description`, 
    IF(n.OriginalImageUrl IS NOT NULL, n.OriginalImageUrl, s.LogoUrl) AS `ImageUrl`, 
    `n`.`Link`, 
    `n`.`PublishedOn`, 
    `s`.`Name` AS `SourceName`, 
    `s`.`Url` AS `SourceWebsite`, 
   s.UrlSlug AS SourceUrlSlug,
    `n`.`Title`
FROM `NewsItems` AS `n`
INNER JOIN `NewsSources` AS `s` ON `n`.`NewsSourceId` = `s`.`Id`
LEFT JOIN `Categories` AS `c` ON `n`.`CategoryId` = `c`.`CategoryId`
WHERE s.UrlSlug = 'slug'
#WHERE s.Id = 52
ORDER BY `n`.`PublishedOn` DESC
LIMIT 50

NewsSources is a table with about 40 rows and NewsItems has ~1 million. Each news item belongs to one source and one source can have many items. I'm trying to get all items for a source identified by URL slug of the source.

1. In case when I use STRAIGHT_JOIN and when I query for a source that has lots of news items, the query returns immediately. However, if I query for a source that has low number of items (~100) OR if I query for a URL slug that doesn't belong to any source (result set is 0 rows), the query runs for 12 seconds.

2. In case when I remove STRAIGHT_JOIN, I see the opposite performance from the first case - it runs really slow when I query for a news source with many items and returns immediately for sources with low number of items or result set is 0, because the URL slug doesn't belong to any news source.

3. In case when I query by news source ID (the commented out WHERE s.Id = 52), the result comes immediately, regardless of whether there are lots of items for that source or 0 items for that source.

I want to point out again that the NewsSources table contains only about 40 rows.

Here is the analyzer results for the query above: Explain Analyzer

What can I do to make this query to run fast always?

Here are tables and indexes definitions:

-- --------------------------------------------------------
-- Server version:               10.4.13-MariaDB-1:10.4.13+maria~bionic - mariadb.org binary distribution
-- Server OS:                    debian-linux-gnu
-- --------------------------------------------------------

-- Dumping structure for table Categories
CREATE TABLE IF NOT EXISTS `Categories` (
  `CategoryId` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `UrlSlug` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `CreatedOn` datetime(6) NOT NULL,
  `ModifiedOn` datetime(6) NOT NULL,
  PRIMARY KEY (`CategoryId`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Dumping structure for table NewsItems
CREATE TABLE IF NOT EXISTS `NewsItems` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `NewsSourceId` int(11) NOT NULL,
  `Title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Link` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PublishedOn` datetime(6) NOT NULL,
  `GlobalId` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CategoryId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `IX_NewsItems_CategoryId` (`CategoryId`),
  KEY `IX_NewsItems_NewsSourceId_GlobalId` (`NewsSourceId`,`GlobalId`),
  KEY `IX_NewsItems_PublishedOn` (`PublishedOn`),
  KEY `IX_NewsItems_NewsSourceId` (`NewsSourceId`),
  FULLTEXT KEY `Title` (`Title`,`Description`),
  CONSTRAINT `FK_NewsItems_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`CategoryId`),
  CONSTRAINT `FK_NewsItems_NewsSources_NewsSourceId` FOREIGN KEY (`NewsSourceId`) REFERENCES `NewsSources` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=649802 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Dumping structure for table NewsSources
CREATE TABLE IF NOT EXISTS `NewsSources` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Url` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UrlSlug` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LogoUrl` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Answer 1

According to POINT - 3 :

In case when I query by news source ID (the commented out WHERE s.Id = 52), the result comes immediately, regardless of whether there are lots of items for that source or 0 items for that source.

This is possible because on using WHERE s.Id = 52 it using index from NewSources & NewITems table do check explain plan might be different then given.

Try Creating Below Index :

create index IDX_UrlSlug on NewsSources(UrlSlug);  

And optimize all three tables :

OPTIMIZE TABLE NewsSources;
OPTIMIZE TABLE NewsItems;
OPTIMIZE TABLE Categories;

Answer 2

Your problem involves what STRAIGHT JOIN does.

Doing a STRAIGHT JOIN may take the Query Optimizer out of the way at some steps.

For example, note what the MySQL Internals Documentation says:

The straightforward use of find_best() and greedy_search() will not apply for LEFT JOIN or RIGHT JOIN. For example, starting with MySQL 4.0.14, the optimizer may change a left join to a straight join and swap the table order in some cases. See also Outer Join Optimization.

Using a STRAIGHT JOIN will process the tables in the order they appear in the query. That is not always good. Why not ???

For example, take a look at this section of your query

FROM `NewsItems` AS `n`
INNER JOIN `NewsSources` AS `s` ON `n`.`NewsSourceId` = `s`.`Id`

Removing STRAIGHT JOIN will let the Query Optimizer examines table and index metrics (i.o. row counts, index cardinalities, etc) for these two tables and decide which one should come first. Doing STRAIGHT JOIN avoids that step by making the Query Optimizer process NewItems table before the NewsSources table all the time, regardless which table has the better metrics.

There are three(3) things I would suggest:

SUGGESTION #1

Don't use STRAIGHT JOIN

SELECT SQL_NO_CACHE
    `c`.`Name` AS `CategoryName`, 
    `c`.`UrlSlug` AS `CategorySlug`, 
    `n`.`Description`, 
    IF(n.OriginalImageUrl IS NOT NULL, n.OriginalImageUrl, s.LogoUrl) AS `ImageUrl`, 
    `n`.`Link`, 
    `n`.`PublishedOn`, 
    `s`.`Name` AS `SourceName`, 
    `s`.`Url` AS `SourceWebsite`, 
   s.UrlSlug AS SourceUrlSlug,
    `n`.`Title`
FROM `NewsItems` AS `n`
INNER JOIN `NewsSources` AS `s` ON `n`.`NewsSourceId` = `s`.`Id`
LEFT JOIN `Categories` AS `c` ON `n`.`CategoryId` = `c`.`CategoryId`
WHERE s.UrlSlug = 'slug'
#WHERE s.Id = 52
ORDER BY `n`.`PublishedOn` DESC
LIMIT 50

SUGGESTION #2

If you still want STRAIGHT JOIN, change the order of the tables:

SELECT SQL_NO_CACHE STRAIGHT_JOIN
    `c`.`Name` AS `CategoryName`, 
    `c`.`UrlSlug` AS `CategorySlug`, 
    `n`.`Description`, 
    IF(n.OriginalImageUrl IS NOT NULL, n.OriginalImageUrl, s.LogoUrl) AS `ImageUrl`, 
    `n`.`Link`, 
    `n`.`PublishedOn`, 
    `s`.`Name` AS `SourceName`, 
    `s`.`Url` AS `SourceWebsite`, 
   s.UrlSlug AS SourceUrlSlug,
    `n`.`Title`
FROM `NewsSources` AS `s`
INNER JOIN `NewsItems` AS `n` ON `n`.`NewsSourceId` = `s`.`Id`
LEFT JOIN `Categories` AS `c` ON `n`.`CategoryId` = `c`.`CategoryId`
WHERE s.UrlSlug = 'slug'
ORDER BY `n`.`PublishedOn` DESC
LIMIT 50

SUGGESTION #3 (OPTIONAL)

If you ever need to run your query with a specific s.Id (such as 52), look up that row before doing any join operations:

SELECT SQL_NO_CACHE
    `c`.`Name` AS `CategoryName`, 
    `c`.`UrlSlug` AS `CategorySlug`, 
    `n`.`Description`, 
    IF(n.OriginalImageUrl IS NOT NULL, n.OriginalImageUrl, s.LogoUrl) AS `ImageUrl`, 
    `n`.`Link`, 
    `n`.`PublishedOn`, 
    `s`.`Name` AS `SourceName`, 
    `s`.`Url` AS `SourceWebsite`, 
   s.UrlSlug AS SourceUrlSlug,
    `n`.`Title`
FROM `NewsItems` AS `n`
INNER JOIN (SELECT * FROM `NewsSources` WHERE Id = 52) AS `s`
ON `n`.`NewsSourceId` = `s`.`Id`
LEFT JOIN `Categories` AS `c` ON `n`.`CategoryId` = `c`.`CategoryId`
WHERE s.UrlSlug = 'slug'
ORDER BY `n`.`PublishedOn` DESC
LIMIT 50

UPDATE 2020-06-14 14:42 EDT

Another suggestion : Move WHERE s.UrlSlug = 'slug' into subquery without STRAIGHT JOIN

SELECT SQL_NO_CACHE
    `c`.`Name` AS `CategoryName`, 
    `c`.`UrlSlug` AS `CategorySlug`, 
    `n`.`Description`, 
    IF(n.OriginalImageUrl IS NOT NULL, n.OriginalImageUrl, s.LogoUrl) AS `ImageUrl`, 
    `n`.`Link`, 
    `n`.`PublishedOn`, 
    `s`.`Name` AS `SourceName`, 
    `s`.`Url` AS `SourceWebsite`, 
    s.UrlSlug AS SourceUrlSlug,
    `n`.`Title`
FROM `NewsItems` AS `n`
INNER JOIN (SELECT * FROM `NewsSources` WHERE s.UrlSlug = 'slug') AS `s`
ON `n`.`NewsSourceId` = `s`.`Id`
LEFT JOIN `Categories` AS `c` ON `n`.`CategoryId` = `c`.`CategoryId`
ORDER BY `n`.`PublishedOn` DESC
LIMIT 50

Answer 3

Solved it by finding the news source ID in a separate query:

    SET @NewsSourceId = NULL;
    
    SELECT Id
    INTO @NewsSourceId
    FROM NewsSources
    WHERE UrlSlug = 'requested-slug';
    
    SELECT @NewsSourceId;
    
    SELECT `c`.`Name` AS `CategoryName`, 
    `c`.`UrlSlug` AS `CategorySlug`, 
    `n`.`Description`, 
    IF(n.OriginalImageUrl IS NOT NULL, n.OriginalImageUrl, s.LogoUrl) AS `ImageUrl`, 
    `n`.`Link`, 
    `n`.`PublishedOn`, 
    `s`.`Name` AS `SourceName`, 
    `s`.`Url` AS `SourceWebsite`, 
    s.UrlSlug AS SourceUrlSlug,
    `n`.`Title`
    FROM `NewsItems` AS `n`
    INNER JOIN `NewsSources` AS `s` ON `n`.`NewsSourceId` = `s`.`Id`
    LEFT JOIN `Categories` AS `c` ON `n`.`CategoryId` = `c`.`CategoryId`
    WHERE @NewsSourceId IS NULL OR s.Id = @NewsSourceId
    ORDER BY `n`.`PublishedOn` DESC
    LIMIT 50

Answer 4

A classic performance problem. But it takes some out-of-the-box thinking to make it perform.

The trick is to build _and maintains an extra table for ordering news articles by category (or byline or topic or ...). Here is a writeup on the details: http://mysql.rjweb.org/doc.php/lists