10

This is my slow query:

SELECT `products_counts`.`cid`
FROM
  `products_counts` `products_counts`

  LEFT OUTER JOIN `products` `products` ON (
  `products_counts`.`product_id` = `products`.`id`
  )
  LEFT OUTER JOIN `trademarks` `trademark` ON (
  `products`.`trademark_id` = `trademark`.`id`
  )
  LEFT OUTER JOIN `suppliers` `supplier` ON (
  `products_counts`.`supplier_id` = `supplier`.`id`
  )
WHERE
  `products_counts`.product_id IN
  (159, 572, 1075, 1102, 1145, 1162, 1660, 2355, 2356, 2357, 3236, 6471, 6472, 6473, 8779, 9043, 9095, 9336, 9337, 9338, 9445, 10198, 10966, 10967, 10974, 11124, 11168, 16387, 16689, 16827, 17689, 17920, 17938, 17946, 17957, 21341, 21352, 21420, 21421, 21429, 21544, 27944, 27988, 30194, 30196, 30230, 30278, 30699, 31306, 31340, 32625, 34021, 34047, 38043, 43743, 48639, 48720, 52453, 55667, 56847, 57478, 58034, 61477, 62301, 65983, 66013, 66181, 66197, 66204, 66407, 66844, 66879, 67308, 68637, 73944, 74037, 74060, 77502, 90963, 101630, 101900, 101977, 101985, 101987, 105906, 108112, 123839, 126316, 135156, 135184, 138903, 142755, 143046, 143193, 143247, 144054, 150164, 150406, 154001, 154546, 157998, 159896, 161695, 163367, 170173, 172257, 172732, 173581, 174001, 175126, 181900, 182168, 182342, 182858, 182976, 183706, 183902, 183936, 184939, 185744, 287831, 362832, 363923, 7083107, 7173092, 7342593, 7342594, 7342595, 7728766)
ORDER BY
  products_counts.inflow ASC,
  supplier.delivery_period ASC,
  trademark.sort DESC,
  trademark.name ASC
LIMIT
  0, 3;

Average query time is 4.5s on my dataset and this is unacceptable.

Solutions i see:

Add all columns from order clause to products_counts table. But i have ~10 order types in application, so i should create a lot of columns and indexes. Plus products_counts have very intensively updates/inserts/deletes, so i need to perform immediately update all order-related columns (using triggers?).

Is there other solution?

Explain:

+----+-------------+-----------------+--------+---------------------------------------------+------------------------+---------+----------------------------------+------+----------------------------------------------+
| id | select_type | table           | type   | possible_keys                               | key                    | key_len | ref                              | rows | Extra                                        |
+----+-------------+-----------------+--------+---------------------------------------------+------------------------+---------+----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | products_counts | range  | product_id_supplier_id,product_id,pid_count | product_id_supplier_id | 4       | NULL                             |  227 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | products        | eq_ref | PRIMARY                                     | PRIMARY                | 4       | uaot.products_counts.product_id  |    1 |                                              |
|  1 | SIMPLE      | trademark       | eq_ref | PRIMARY                                     | PRIMARY                | 4       | uaot.products.trademark_id       |    1 |                                              |
|  1 | SIMPLE      | supplier        | eq_ref | PRIMARY                                     | PRIMARY                | 4       | uaot.products_counts.supplier_id |    1 |                                              |
+----+-------------+-----------------+--------+---------------------------------------------+------------------------+---------+----------------------------------+------+----------------------------------------------+

Tables structure:

CREATE TABLE `products_counts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `supplier_id` int(11) unsigned NOT NULL,
  `count` int(11) unsigned NOT NULL,
  `cid` varchar(64) NOT NULL,
  `inflow` varchar(10) NOT NULL,
  `for_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cid` (`cid`),
  UNIQUE KEY `product_id_supplier_id` (`product_id`,`supplier_id`),
  KEY `product_id` (`product_id`),
  KEY `count` (`count`),
  KEY `pid_count` (`product_id`,`count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `external_id` varchar(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category_id` int(11) unsigned NOT NULL,
  `trademark_id` int(11) unsigned NOT NULL,
  `photo` varchar(255) NOT NULL,
  `sort` int(11) unsigned NOT NULL,
  `otech` tinyint(1) unsigned NOT NULL,
  `not_liquid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `applicable` varchar(255) NOT NULL,
  `code_main` varchar(64) NOT NULL,
  `code_searchable` varchar(128) NOT NULL,
  `total` int(11) unsigned NOT NULL,
  `slider` int(11) unsigned NOT NULL,
  `slider_title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `external_id` (`external_id`),
  KEY `category_id` (`category_id`),
  KEY `trademark_id` (`trademark_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `trademarks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `external_id` varchar(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `country_id` int(11) NOT NULL,
  `sort` int(11) unsigned NOT NULL DEFAULT '0',
  `sort_list` int(10) unsigned NOT NULL DEFAULT '0',
  `is_featured` tinyint(1) unsigned NOT NULL,
  `is_direct` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `external_id` (`external_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `external_id` varchar(36) NOT NULL,
  `code` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  `delivery_period` tinyint(1) unsigned NOT NULL,
  `is_default` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `external_id` (`external_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL server information:

mysqld  Ver 5.5.45-1+deb.sury.org~trusty+1 for debian-linux-gnu on i686 ((Ubuntu))
  • 3
    Can you provide a SQL Fiddle with indexes, table schema, and test data? Also what is your target time? Are you looking to get it to complete in 3 seconds, 1 second, 50 milliseconds? How many records do you have in the various tables 1k, 100k, 100M? – Erik Oct 5 '15 at 21:01
  • If those fields you're sorting by aren't indexed and the data set it really large could you perhaps be looking at a sort_buffer_size issue? You could try modifying your the value on your session and run the query to see if it improves. – Brian Efting Oct 7 '15 at 3:57
  • Have you tried adding an index on (inflow, product_id)? – ypercubeᵀᴹ Oct 8 '15 at 7:42
  • Make sure you have a decent innodb_buffer_pool_size. Typically about 70% of available RAM is good. – Rick James Oct 24 '15 at 20:45
6
+100

Reviewing your table definitions shows that you have indexes matching across the tables involved. This should cause the joins to happen as quickly as possible within the limits of MySQL's join logic.

However, sorting from multiple tables is more complex.

In 2007 Sergey Petrunia described the 3 MySQL sorting algorithms in order of speed for MySQL at: http://s.petrunia.net/blog/?m=201407

  1. Use index-based access method that produces ordered output
  2. Use filesort() on 1st non-constant table
  3. Put join result into a temporary table and use filesort() on it

From the table definitions and joins shown above, you can see that you will never get the fastest sort. That means that you will be dependent on filesort() for the sort criteria you are using.

However, if you design and use a Materialized View you will be able to use the fastest sort algorithm.

To see the details defined for MySQL 5.5 sorting methods see: http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

For MySQL 5.5 (in this example) to increase ORDER BY speed if you cannot get MySQL to use indexes rather than an extra sorting phase, try the following strategies:

• Increase the sort_buffer_size variable value.

• Increase the read_rnd_buffer_size variable value.

• Use less RAM per row by declaring columns only as large as needed for the actual values to be stored. [E.g. Reduce a varchar(256) to varchar(ActualLongestString)]

• Change the tmpdir system variable to point to a dedicated file system with large amounts of free space. (Other details are offered in the link above.)

There is more detail provided in the MySQL 5.7 documentation to increase ORDER speed, some of which may be slightly upgraded behaviors:

http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

Materialized Views - A Different Approach to Sorting Joined Tables

You alluded to Materialized Views with your question referring to using triggers. MySQL has no built in functionality to create a Materialized View but you do have the tools needed. By using triggers to spread the load you can maintain the Materialized View up to the moment.

The Materialized View is actually a table which is populated through procedural code to build or rebuild the Materialized View and maintained by triggers to keep the data up-to-date.

Since you are building a table which will have an index, then the Materialized View when queried can use the fastest sort method: Use index-based access method that produces ordered output

Since MySQL 5.5 uses triggers to maintain a Materialized View, you will also need a process, script, or stored procedure to build the initial Materialized View.

But that is obviously too heavy a process to run after each update to the base tables where you manage the data. That is where the triggers come into play to keep the data up-to-date as changes are made. This way each insert, update, and delete will propagate their changes, using your triggers, to the Materialized View.

The FROMDUAL organization at http://www.fromdual.com/ has sample code for maintaining a Materialized View. So, rather than write my own samples I will point you to their samples:

http://www.fromdual.com/mysql-materialized-views

Example 1: Building a Materialized View

DROP TABLE sales_mv;
CREATE TABLE sales_mv (
    product_name VARCHAR(128)  NOT NULL
  , price_sum    DECIMAL(10,2) NOT NULL
  , amount_sum   INT           NOT NULL
  , price_avg    FLOAT         NOT NULL
  , amount_avg   FLOAT         NOT NULL
  , sales_cnt    INT           NOT NULL
  , UNIQUE INDEX product (product_name)
);

INSERT INTO sales_mv
SELECT product_name
    , SUM(product_price), SUM(product_amount)
    , AVG(product_price), AVG(product_amount)
    , COUNT(*)
  FROM sales
GROUP BY product_name;

This gives you the Materialized View at the moment of the refresh. However, since you have a fast moving database, you also want to keep this view as up-to-date as possible.

Therefore the base data tables affected need to have triggers to propagate the changes from a base table to the Materialized View table. As one example:

Example 2: Inserting New Data Into a Materialized View

DELIMITER $$

CREATE TRIGGER sales_ins
AFTER INSERT ON sales
FOR EACH ROW
BEGIN

  SET @old_price_sum = 0;
  SET @old_amount_sum = 0;
  SET @old_price_avg = 0;
  SET @old_amount_avg = 0;
  SET @old_sales_cnt = 0;

  SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0)
       , IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0)
    FROM sales_mv
   WHERE product_name = NEW.product_name
    INTO @old_price_sum, @old_amount_sum, @old_price_avg
       , @old_amount_avg, @old_sales_cnt
  ;

  SET @new_price_sum = @old_price_sum + NEW.product_price;
  SET @new_amount_sum = @old_amount_sum + NEW.product_amount;
  SET @new_sales_cnt = @old_sales_cnt + 1;
  SET @new_price_avg = @new_price_sum / @new_sales_cnt;
  SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;

  REPLACE INTO sales_mv
  VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg
       , @new_amount_avg, @new_sales_cnt)
  ;

END;
$$
DELIMITER ;

Of course, you will also need triggers to maintain Deleting Data From a Materialized View and Update Data In a Materialized View. Samples are available for these triggers as well.

AT LAST: How Does That Make Sorting Joined Tables Faster?

The Materialized View is being built constantly as the updates are made to it. Therefore you can define the Index (or Indexes) that you want to use for sorting the data in the Materialized View or Table.

If the overhead of maintaining the data is not too heavy, then you are spending some resources (CPU/IO/etc) for each relevant data change to keep the Materialized View and thus the index data is up-to-date and readily available. Therefore, the select will be faster, since you:

  1. Already spent incremental CPU and IO to get the data ready for your SELECT.
  2. The index on the Materialized View can use the fastest sorting method available to MySQL, namely Use index-based access method that produces ordered output.

Depending on your circumstances and how you feel about the overall process, you might want to rebuild the Materialized Views every night during a slow period.

Note: In Microsoft SQL Server Materialized Views are referred to Indexed Views and are automatically updated based on the Indexed View's metadata.

6

There's not a whole lot to go on here, but I'm guessing the primary issue is that you're creating a fairly large temporary table and sort file on disk each time. The reason being:

  1. You're using UTF8
  2. You're using some large varchar(255) fields for sorting

This means that your temporary table and sort file could be fairly large, as when creating the temporary table the fields are created at the MAX length, and when sorting the records are all at the MAX length (and UTF8 is 3 bytes per character). These are also likely precluding the use of an in-memory temporary table. For more info, see internal temp tables details.

The LIMIT also does us no good here, as we need to materialize and order the entire result set before we know what the first 3 rows are.

Have you tried moving your tmpdir to a tmpfs filesystem? If /tmp is not already using tmpfs (MySQL uses tmpdir=/tmp by default on *nix), then you could use /dev/shm directly. In your my.cnf file:

[mysqld]
...
tmpdir=/dev/shm  

Then you would need to restart mysqld.

That could make a huge difference. If you're likely to come under memory pressure on the system, you probably want to cap the size though (typically linux distros cap tmpfs at 50% of total RAM by default) in order to avoid swapping memory segments out to disk, or even worse an OOM situation. You can do that by editing the line in /etc/fstab:

tmpfs                   /dev/shm                tmpfs   rw,size=2G,noexec,nodev,noatime,nodiratime        0 0

You can resize it "online" too. For example:

mount -o remount,size=2G,noexec,nodev,noatime,nodiratime /dev/shm

You could also upgrade to MySQL 5.6--which has performant subqueries and derived tables--and play around with the query a bit more. I don't think we'll see big wins going that route though, from what I see.

Good luck!

  • Thanks for your answer. Moving tmpdir to tmpfs gave a good perfomance gain. – happyproff Oct 13 '15 at 1:13

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.