MySQL query SLOW Don't know how to optimize

mysql query slow on large table
mysql slow query
mysql query optimization
mysql performance schema slow queries
mysql show slow queries
how to make mysql query faster
why is my sql query so slow
mysql query running very slow

MySQL query SLOW don’t know how to optimize

I think I m fine with hardware 60GB RAM 10 Cores SSD

Hi I m having a big issue with this query running slow on Mysql they query is below:

# Thread_id: 1165100  Schema: back-Alvo-11-07-19  QC_hit: No
# Query_time: 9.015205  Lock_time: 0.000188  Rows_sent: 1  Rows_examined: 2616880
# Rows_affected: 0
SET timestamp=1568549358;
SELECT count(*) as total_rows FROM(
(SELECT m.*
FROM phpfox_channel_video AS m
INNER JOIN phpfox_channel_category AS mc
    ON(mc.category_id = mc.category_id)
INNER JOIN phpfox_channel_category_data AS mcd
    ON(mcd.video_id = m.video_id)

WHERE m.in_process = 0 AND m.view_id = 0 
     AND m.module_id = 'videochannel' 
    AND m.item_id = 0 AND m.privacy IN(0) 
    AND mcd.category_id = 17
GROUP BY m.video_id
ORDER BY m.time_stamp DESC
LIMIT 12

)) AS m
JOIN phpfox_user AS u
    ON(u.user_id = m.user_id);

This query is running very slow as you can see 9 seconds

When looking for online help to optimize queries always talk about adding indexes,

as you can see below for EXPLAIN statment I already have indexes

Do you guys have any Idea where I should look to improve speed os this query? I m not DB guy having hard time with this. This is a website and have 400,000 videos.

Thanks

The explain shows that you are not using an index on table phpfox_channel_video as m, and that it is using a temporary index on table phpfox_channel_category AS mc, which means it is not using an index, but is building an index first, which takes considerable time.

Also, the index for table phpfox_channel_category_data AS mcd could be better.

The indexes you need are:

CREATE INDEX idx_cat_data_video_id ON phpfox_channel_category_data
  (category_id, video_id);
CREATE INDEX idx_channel_cat_id ON phpfox_channel_category (category_id);
CREATE INDEX idx_video_mult ON phpfox_channel_video
  (in_process, view_id, module_id, item_id, privacy, video_id, time_stamp);

MySQL query suddenly slow down, Database slow? Improve the speed and scalability of your RDBMS with these 21 rules for faster SQL queries. | Keep up with the hottest topics� If the server is slow I the query and it takes about 4 seconds. When I look at the tables, they look OK, no data_free, all Indices have correct cardinality. Anyhow if I optimize the table the query takes about 4 ms.

10 essential MySQL performance tuning tips, Checking Your Slow Query Logs. MySQL has the capability to filter and log slow queries. There are various ways you can investigate these, but� Slow queries are sometimes unavoidable, but, often, slow queries are missing an index. Use the slow-query log to find potential issues. When analyzing a system to find problems, putting: log-queries-not-using-indexes. in the my.cnf file and restarting mysql will log the unindexed queries to the slowquery log. What can be indexed? The rule of

Let's make sure that we are optimizing the right query. I suggest we check this condition in the ON clause:

 mc.category_id = mc.category_id

We know that's going to be TRUE for every row in mc with a non-NULL value of category_id. We could express that condition as:

 mc.category_id IS NOT NULL

This means the join is almost a cross join; every row returned from m matched with every row from mc. That is, we could get an equivalent result writing:

   FROM phpfox_channel_video    m
   JOIN phpfox_channel_category    mc
     ON mc.category_id IS NOT NULL

I suspect that's not actually the result we're after. I think we were meaning to match to m.category_id. But that's just a guess.


If video_id column is PRIMARY KEY or UNIQUE KEY on m, we can avoid the potentially expensive GROUP BY operation by avoiding the joins that create duplicated rows, by using EXISTS with correlated subqueries. If we can avoid generating an intermediate result with duplicate values of video_id, then we can avoid the need to do the GROUP BY.

Also, for the inline view query, rather than return all columns * we can return just the expressions that we need. In the outer query, the only column referenced is user_id.

So we could write something like this:

SELECT COUNT(*) AS total_rows
  FROM (
         SELECT m.user_id
           FROM phpfox_channel_video m
          WHERE EXISTS ( SELECT 1
                           FROM phpfox_channel_category mc
                          WHERE mc.category_id  = m.category_id
                      --        mc.category_id = mc.category_id  -- <original
                       )
            AND EXISTS ( SELECT 1
                           FROM phpfox_channel_category_data mcd
                          WHERE mcd.video_id     = m.video_id
                            AND mcd.category_id  = 17
                       )
            AND m.in_process  = 0
            AND m.view_id     = 0
            AND m.module_id   = 'videochannel'
            AND m.item_id     = 0
            AND m.privacy   IN (0)
          ORDER BY m.time_stamp DESC
          LIMIT 12
       ) d
    JOIN phpfox_user u
      ON u.user_id = d.user_id

For tuning, optimal index for m will have leading columns that have equality predicates, followed by the time_stamp column so that we can avoid a "Using filesort" operation, the ORDER BY can be satisfied by returning rows in index order. It looks like the reason we need the rows ordered is for the LIMIT clause.

... ON phpfox_channel_video (in_process, view_id, item_id, module_id
          , time_stamp, video_id, ... )

The other two tables, we want indexes with leading columns that have equality predicates

... ON phpfox_channel_category_data (video_id, category_id, ...)

... ON phpfox_channel_category ( category_id, ... )

NOTES:

(It's not entirely clear why we need an inline view, and we are delaying the join from the user_id reference. Then again, the point of the entire query isn't really obvious to me; I'm just providing a re-write, given the provided SQL, with the change to the condition category_id.)

The above assumed that category_id column exists in m, and that it's a one-to-many relationship.

But if that's not true... if the mcd table is actually junction table, resolving a many-to-many relationship between video and category, such that the join condition was meant to be

 mcd.category_id = mc.category_id 
   ^

Then we would want to replace the WHERE EXISTS and AND EXISTS in the query above, into a single correlated subquery. Something like this:

SELECT COUNT(*) AS total_rows
  FROM (
         SELECT m.user_id
           FROM phpfox_channel_video m
          WHERE EXISTS ( SELECT 1
                           FROM phpfox_channel_category mc
                           JOIN phpfox_channel_category_data mcd
                             ON mcd.category_id  = mc.category_id 
                          WHERE mcd.video_id     = m.video_id
                            AND mcd.category_id  = 17
                       )
            AND m.in_process  = 0
            AND m.view_id     = 0
            AND m.module_id   = 'videochannel'
            AND m.item_id     = 0
            AND m.privacy   IN (0)
          ORDER BY m.time_stamp DESC
          LIMIT 12
       ) d
    JOIN phpfox_user u
      ON u.user_id = d.user_id

How to Identify MySQL Performance Issues with Slow Queries , You didn't post output from EXPLAIN so this is guesswork. But it seems possible the conditions of your first query are somehow forcing MySQL's� After enabling the slow query log, save and close the file. Then restart the MySQL service: sudo systemctl restart mysql With these settings in place, you can find problematic query statements by viewing the slow query log. You can do so with less, like this: sudo less /var/log/mysql_slow.log

Why does this MySQL query slow down when more conditions are , The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to� MySQL has a built-in slow query log. To use it, open the my.cnf file and set the slow_query_log variable to "On." Set long_query_time to the number of seconds that a query should take to be considered slow, say 0.2. Set slow_query_log_file to the path where you want to save the file.

MySQL 5.7 Reference Manual :: 5.4.5 The Slow Query Log, Part of this demo is for basic mysql trouble shooting including use of the slow query log. I've generated a database and installed our app but its a clean database� A few days ago mysql became suddenly very slow with queries taking more than 10s to complete when they used to complete under 100ms It lasted about an hour and then the speed was normal again. What could cause such a problem? A few more information: The mysql version is 5.0.51a The mysql database is on a dedicated box with 1.5 GB ram.

How can artificially create a slow query in mysql?, Using The slow query log. Another valuable tool in MySQL is the included slow query logging feature. This feature is the preferred method for� The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.

Comments
  • "MySQL query SLOW Don't know how to optimize" We also don't have noooo idea without SHOW CREATE TABLE <table_name> foreach table and a EXPLAIN <query> outputs..
  • your picture is barely readable, try to add a text version instead or a better image
  • Also SELECT m.* ... GROUP BY m.video_id ORDER BY m.time_stamp DESC might be used wrong, check the manual how MySQL handles detection off functional dependency when using GROUP BY and check if you are using it correctly .. With correctly i mean atleast running MySQL 5.7.5+ and a running ONLY_FULL_GROUP_BY sql_mode otherwise the query can not generated trustworthy results..
  • Hi Marlin could you check this one?
  • The same indexes should work for that query too. There is nothing you can do to index the LIKE expressions.
  • Thanks Marlin I already applied your answer, did not see much improvement for performance do not know where to look at anymore, if you have any guidance where should I look to fix this would help.
  • Then I wonder if it is the network connection between your client and the server.
  • i think this answer misses the mark. before we start throwing around suggestions for indexes, lets review the SQL statement for signs that what we are trying to tune might not be returning the result we expect, and for signs that it may be forcing inefficient operations.
  • yes. avoid generating an intermediate set and then a GROUP BY to collapse the rows, if we know video_id is the PRIMARY KEY of m. I think another big performance problem is the condition mc.category_id` equals itself... looks like mcd is meant to function as a junction table, resolving the many-to-many between video and category. But we're just guessing. I totally agree; before we start throwing out suggestions for indexes, lets make sure we are running the statement we want to run.
  • Thanks for the help I appreciated it. I tried to run yout suggested query and got the error MySQL said: #1054 - Unknown column 'mcd.category' in 'on clause'
  • looks like column reference might be mcd.category_id . i did not mean to provide a SQL solution for you; my intent was to get you to think a little more clearly about what result we actually want to return. And in terms of a specification, I have nothing to go on in except the SQL that was provided, and the matching mc.category_id = mc.category_id. As far as which other table actually contains a column that should be matched to mc.category_id, I'm just guessing. The last query is based on mcd being a junction table, a many-to-many relationship between m and mc. Just guessing.
  • To echo the beginning of the answer... lets be sure that we are tuning the statement, be sure that the statement is returning the result we actually want to return, and be sure that we are using patterns that give us the best shot at good performance (e.g. avoid creating a huge intermediate resultset and forcing a Using filesort operation to collapse rows.) Yes, adding indexes can improve performance, but only so much; often times the biggest performance bang comes from re-writing the query.
  • If mcd is a junction table, then likely we already have foreign keys defined, and for InnoDB, we already have indexes to support the constraints. Before we make recommendations about adding indexes, we really need to know what indexes already exist. For example, if the (video_id,category_id) tuple is unique, then we probably want UNIQUE index on both (video_id,category_id) and on (video_id,category_id) and we would want to drop any singleton indexes on just video_id and just category_id. Creating suitable indexes is an important part of performance tuning, but its just one part.
  • Thanks for your time spencer. I thought this performance issue could be something more basic that I would be missing, but as I am not DB guy to apply all your reasoning and thinking is not possible for me. I would have to be advance level on Database related stuff or at least have a better knowledge than I have. At the end of all this. What I have done is fine tune my.CNF file and looked at indexes and it did not help very much. It seems more about structure and development itself. Thank you very much.