2

I have a structure similar to this one:

CREATE TABLE `author` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE `book` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `author_id` int(11) unsigned NOT NULL,
  `org` int(11) unsigned NOT NULL,
  `country` char(3) NOT NULL,
  `publish_date` date NOT NULL,
  `price` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `publish_date` (`publish_date`),
  KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
  KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

Here is a SQL Fiddle

So I'm trying to execute a query with two range conditions

SELECT
  id as author_id,
  (SELECT COUNT(DISTINCT `book`.`id`)+1 
   FROM `book` 
   WHERE 
      `book`.`org` = 1
      AND `book`.`country` = 'USA' 
      AND `book`.`publish_date` BETWEEN '2010-04-30' AND '2011-04-30'
      AND `book`.`author_id` = `author`.`id`
      AND `book`.`price` < 50
  ) AS `books_under_fifty`
FROM `author` 
ORDER BY books_under_fifty desc;

but the optimizer uses only part of my index: const,const,db_9_6349e2.author.id from i0

is there a way to optimize it?

3

Well, let's think out of the box. It seems that there are two sets of results -- the authors with some such books, and those without. The first case is more efficiently done via:

SELECT  author_id,
        COUNT(*)+1 AS books_under_fifty
    FROM  `book`
    WHERE  `org` = 1
      AND  `country` = 'USA'
      AND  publish_date >= '2010-05-01'
      AND  publish_date <  '2010-05-01' + INTERVAL 1 YEAR
      AND  `price` < 50 

That would probably be best served by

INDEX(country, org, publish_date, price, author_id)

If you are happy with the performance of that, then you can think out of the box in finding "the rest of the authors".

Back to the 2-ranges problem. That can sometimes be solved by Partitioning. Not knowing the distribution of the data (is one year a small subset of the total? or what about price?), I can't say which would be better:

PARTITION BY RANGE(TO_DAYS(publish_date))

Partitioning by price is out, since DECIMAL can't be used. Storing price as number of cents and using PARTITION BY RANGE(cents) would work, but clumsily.

Then make 20-50 partitions of the partition key. This would give you "partition pruning" for one 'range', but then the index needs to be tweaked for the other. Assuming you partition by publish_date:

INDEX(country, org, price)
PRIMARY KEY(id, publish_date)

Meanwhile, ...

Don't use utf8 if you are using standard country_codes; use ascii, at least for that column.

What is org does it distinguish the book? Or are there some missing columns? Is there no other "unique" column(s)?

Another variant that might be better:

 PRIMARY KEY(country, org, price,  -- for clustering
             publish_date,   -- because partitioning requires it
             id)           -- to assure uniqueness
 INDEX(id)

This variant assumes you have no 'natural' PK. And it will be more efficient because it clusters the desired rows together. I did not include author_id in the PK since it is readily available in the row.

add index

ADD INDEX ac(author_id, country) tricked it into using i0. Go figure. Note: I left (author_id) intact and first in the list of indexes; so it is not just the order of the indexes.

I'm using Percona's 5.6.22-71.0-log .

Note that there are only 4 different country values, evenly distributed. But there are many different author_id, making it by itself more "selective".

  • Thank you for you answer @Rick James, partitioning looks like a big topic, and I know nothing about it, you just gave me a subject to read, until I understand you answer. About the distribution: rows are evenly distributed across years, ~1 million rows gets into a year range, org is like organization but the rows are not evenly distributed, more like 60%/15%/15% for the top 3 (in this case we always filter against the top1). Country can be considered as a field which distribute the rows into 20+ groups, there the top one get 5% of the records. – Todor Jul 7 '17 at 22:07
  • Is there an ISBN column? Or something else that uniquely identifies the book? – Rick James Jul 7 '17 at 22:15
  • And finally price: rows are normally distributed across price, but only in the context of single org+country, i.e. there is no relation between prices outside org+country. e.g. prices between 1-USA are normally distributed in the range of 20-40, but prices between 2-USA could be normally distributed in the range of 40-80, etc. – Todor Jul 7 '17 at 22:15
  • Oh, so this is a table of book_prices, not books. There is another table of books? – Rick James Jul 7 '17 at 22:17
  • I think a combination of all fields (org, country, author, date, price) would uniquely identify a row. And no, there is no books, but to be honest in our real world data, its not about books at all I renamed the models + fields to something more generic (author/book), which I think would be still relevant in term of parent-child relation, but I guess I may be wrong. – Todor Jul 7 '17 at 22:28
1

You could change your subquery to a LEFT JOIN, and GROUP BY:

SELECT
    author.id as author_id, (count(distinct book.id) + 1) AS books_under_fifty
FROM
    author
    LEFT JOIN book ON
          book.author_id = author.id
      AND book.org = 1
      AND book.country = 'USA' 
      AND book.publish_date BETWEEN '2010-04-30' and '2011-04-30'
      AND book.price < 50
GROUP BY
    author.id
ORDER BY 
     books_under_fifty desc, author_id;

... but it really doesn't make any difference with regard to execution plan.

You can check everything at dbfiddle here

So, I'm afraid the answer is most probably, you cannot optimize any further. I'd recommend, in any case, to test with real data, not with the simulation I used.

NOTES:

  1. I've added a second ORDER BY expression, to make sure the order is fully deterministic.
  2. Checked with MariaDB, instead of MariaDB first. MySQL uses two different plans, but it even makes a poorer choice (Check at http://rextester.com/JDHP57216)

You can also check a variation of the LEFT JOIN:

SELECT
    author.id as author_id, coalesce(ccc, 1) AS books_under_fifty
FROM
    author
    LEFT JOIN 
    (SELECT 
        author_id, count(distinct book.id) + 1 AS ccc
    FROM
        book 
    WHERE
            book.org = 1
        AND book.country = 'USA' 
        AND book.publish_date BETWEEN '2010-04-30' and '2011-04-30'
        AND book.price < 50
     GROUP BY
          author_id
     ) AS q0
     ON q0.author_id = author.id
ORDER BY 
     books_under_fifty desc, author_id;

But, again, it doesn't seem MariaDB gets any advantage.

dbfiddle here


Another Open Source Database (PostgreSQL 9.6) can handle things in a far more sophisticated way and can give you execution plans much nicer... (and most probably, faster, although MariaDB didn't get any timings).

PostgreSQL can make the LEFT JOIN work faster (in most of the cases, although that depends on the randomness of each execution).

dbfiddle here

  • Thank you for your answer @joanolo, actually the order does not matter (I added it just for representation in the SQL Fiddle), In my real word query I get the exact same query plan (using the index only partially). Moving to PostgreSQL for now would be impossible, but ye I keep reading good staff about it, so maybe one day in the future..). Using a Left join with group by will be hard too, because of the ORM which we use to build this one. This is actually only a part from a bigger query, but since this the main issue of the slowness I posted only this part. – Todor Jul 7 '17 at 20:30
  • ORM don't always make your life easier... Does it have some kind of SQL pass-through? Some do – joanolo Jul 7 '17 at 20:33
  • 1
    yes there is, actually this is the ORM which we use ;) But as you mention it really doesn't make any difference with regard to execution plan, however i will give it a try tomorrow. The child table in our real data is a few million records big and the initial execution of this query may took up to 20-30 seconds and cause gateway timeouts for some users. After that, its starts to get faster (300-400ms). – Todor Jul 7 '17 at 20:41
  • I'd say "in case of doubt, test all possibilities"... There's no clear winner beforehand in this case. I guess it starts to get faster when you have most of your data cached. – joanolo Jul 7 '17 at 20:46

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.