Mysql takes more time in sending data

Question

I have this particular query which takes long time to execute, other queries on the same tables execute very fast. Querycache is enabled in mysql but still the below query takes more than 80 seconds everytime and CPU crosses 100% utilization.

I cannot modify the query because it is generated by Drupal. Is there anything else I can do to improve performance?

The query is:

select count(*) 
from (
    SELECT slk.key_id AS key_id 
    FROM slk slk  
        LEFT JOIN users users ON    slk.uid = users.uid 
        LEFT JOIN node node_users ON users.uid = node_users.uid 
            AND   node_users.type = 'profile'
) count_alias;

Below is the profile information:

+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000029 | 
| checking query cache for query |  0.000093 | 
| Opening tables                 |  0.000210 | 
| System lock                    |  0.000007 | 
| Table lock                     |  0.000075 | 
| optimizing                     |  0.000008 | 
| statistics                     |  0.000113 | 
| preparing                      |  0.000027 | 
| executing                      |  0.000004 | 
| Sending data                   | 66.086903 | 
| init                           |  0.000027 | 
| optimizing                     |  0.000009 | 
| executing                      |  0.000018 | 
| end                            |  0.000003 | 
| query end                      |  0.000004 | 
| freeing items                  |  0.000049 | 
| storing result in query cache  |  0.000116 | 
| removing tmp table             |  0.033162 | 
| closing tables                 |  0.000106 | 
| logging slow query             |  0.000003 | 
| logging slow query             |  0.000085 | 
| cleaning up                    |  0.000007 | 
+--------------------------------+-----------+

explain on the query gives:

| id | select_type | table      | type   | possible_keys         | key     | key_len | ref             | rows  | Extra                        |
|  1 | PRIMARY     | NULL       | NULL   | NULL                  | NULL    | NULL    | NULL            |  NULL | Select tables optimized away | 
|  2 | DERIVED     | slk        | ALL    | NULL                  | NULL    | NULL    | NULL            | 55862 |                              | 
|  2 | DERIVED     | users      | eq_ref | PRIMARY               | PRIMARY | 4       | gscom.slk.uid   |     1 | Using index                  | 
|  2 | DERIVED     | node_users | ref    | node_type,uid,idx_ctp | uid     | 4       | gscom.users.uid |     3 |                              |

idx_ctp is an index on (uid, type).

The query cache is working and below are the stats.

show variables like '%query_cache%';:

| Variable_name                | Value    |
| have_query_cache             | YES      | 
| query_cache_limit            | 2097152  | 
| query_cache_min_res_unit     | 4096     | 
| query_cache_size             | 52428800 | 
| query_cache_type             | ON       | 
| query_cache_wlock_invalidate | OFF      |

mysql> show status like '%Qcache%';:

| Variable_name           | Value    |
| Qcache_free_blocks      | 1255     | 
| Qcache_free_memory      | 22902848 | 
| Qcache_hits             | 1484908  | 
| Qcache_inserts          | 1036344  | 
| Qcache_lowmem_prunes    | 95086    | 
| Qcache_not_cached       | 3975     | 
| Qcache_queries_in_cache | 14271    | 
| Qcache_total_blocks     | 30117    |

Why don't you just rewrite that query into something more meaningful? If you want to see which steps are taking time, use SET PROFILING = 1; SELECT...; SHOW PROFILE FOR QUERY 1; SET PROFILING = 0;. — N.B.
– N.B., Commented Apr 20, 2012 at 11:13
sending data takes most of the time, i have tuned mysql giving more to querycache but still this query is not getting cached and takes 80 seconds everytime, rest of the functionalities in my drupal site does not work in the mean time — Pavan
– Pavan, Commented Apr 20, 2012 at 11:24
Can a slk have many users? And can a user have many nodes of type 'profile'? In other words, are those joins really necesary? Also, try writting count(1) instead of count(*). — Alejandro B.
– Alejandro B., Commented Apr 20, 2012 at 12:30
@Pavan: What are the datatypes of the columns (used by the query)? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 23, 2012 at 20:50

ypercubeᵀᴹ · Accepted Answer · 2012-04-20 11:33:51Z

2

You need indexes on:

table slk: (uid)
table node_users: (type, uid)

The query can be rewritten without subquery, as:

SELECT COUNT(*) 
FROM slk 
    LEFT JOIN users 
        ON slk.uid = users.uid 
    LEFT JOIN node node_users 
        ON  users.uid = node_users.uid 
        AND node_users.type = 'profile'

And I'm really not sure why you use LEFT JOIN. You can probably use INNER JOIN and have the same result. Or just use the simple:

SELECT COUNT(*) 
FROM slk

edited Apr 20, 2012 at 11:33

answered Apr 20, 2012 at 11:25

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Pavan Over a year ago

sorry for not mentioning it more clearly, i already have those indexes which are reflected in the explain information.

ypercubeᵀᴹ Over a year ago

You say you have an index on (uid, type). I mean an index on (type, uid). The order matters.

Pavan Over a year ago

This query is automatically generated by drupal[link](drupal.org) views, so there is little handle with me to modify the query. The same type of join is used other queries as well but they gets executed very fast(few ms to 1 sec). Why is this query is not cached in mysql?

Pavan Over a year ago

sure, i will add the index on (type, uid) and check the result. Thanks for your reply.

ypercubeᵀᴹ Over a year ago

And check that you do have an index on slk(uid).

|

Marcus Adams · Accepted Answer · 2012-04-20 14:06:24Z

This is a poor query. It selects all 55862 rows from the slk table and joins all 55862 rows to two other tables.

JOINs on large result sets are performance killers because MySQL must, at best, perform a seek for each row in the master table to the corresponding rows in the detail table. If there are too many rows, MySQL will decide it's just faster to scan the entire detail table rather than perform so many seeks.

Creating a multi-column index on node_users: (uid, type), as ypercube suggested, will help the second join (to the node_users) table.

Ideally, if this query were using INNER JOINs instead of LEFT OUTER JOINs, we could optimize the query by allowing MySQL to traverse it backwards, starting with the AND node_users.type = 'profile' and giving it the index that ypercube suggested, in the order he suggested. However, since they are left joins, MySQL will still want to get all rows in the slk table, and will start from there.

The only additional thing you can do to improve the performance of this query without modifying it is to avoid hitting the table data by using covering indexes.

This will use a lot more memory, but hopefully, it will be faster because it can read all the values from the indexes (in memory) rather than hitting the disk. This implies that you have enough RAM to support having all of the indexes in memory and you've configured MySQL to use it.

You already have a covering index on users (see Using index in the EXPLAIN result). You want all three lines of the DERIVED query to say Using index in the Extra column.

Create the additional following covering index:

slk: (key_id, uid)

This one was already mentioned above, but I'm including it here again so you don't forget it:

node_users: (uid, type)

You won't get breakthrough performance here because you're still having to do all of the JOINs, but you will get some improvement. Let us know how much faster it is. I'm guessing about twice as fast.

Collectives™ on Stack Overflow

Mysql takes more time in sending data

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related