3

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

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;

| improve this answer | |
1

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
| improve this answer | |
  • Thank you for your explanation what STRAIGHT_JOIN is doing Rolando. However, this does not help. Please see my point 1 & 2 about the difference between with and without it. What is even weirder, if I change the order of the tables, then I get the opposite behavior for points 1 & 2... Your suggestion #3 is obviously gonna help. I can keep the list of News Sources in memory, and query always by NewsSourceId, though keeping things in memory introduces new dimensions of challenges which I want to avoid at this early point in the project. I really want to get to the bottom of the problem here... – Gorgi Rankovski Jun 10 at 20:18
  • You are right about STRAIGHT_JOIN is messing up with the query optimizer. When I remove it and if in the WHERE I filter by NewsSources.Id instead of the URL slug, the query plan is different for different IDs - basically for those sources with little news items it uses one query plan, for those with lots of news items it uses a different query plan. If I have UrlSlug in the Where filter instead, then it always uses the same query plan, which explains why it is slow for some values and fast for the others and why STRAIGHT_JOIN is opposite as it uses a different query plan when I add it back... – Gorgi Rankovski Jun 10 at 20:44
  • @GorgiRankovski did you ever solve this ??? – RolandoMySQLDBA Jun 14 at 14:40
  • Rolando I have not. Please see my two comments above. – Gorgi Rankovski Jun 14 at 18:09
  • You may need to put the s.UrlSlug = 'slug' for in the s subquery – RolandoMySQLDBA Jun 14 at 18:39
1

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
| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.