SQL - Query Performance

Question

How to find the inefficient queries in mysql database ? I want to do performance tuning on my queries , but i coudn't find where my queries are located ? Please suggest me where can i find mysql queries for my tables .

Thanks

Prabhakaran.R

they are in mysql stored procedures/functions/events/triggers, or most likely passed thru from your source code. programming language. php. what are you using ? — Drew
– Drew, Commented Jul 22, 2015 at 3:38
post any particularly slow ones with schema, we can give it a go :> — Drew
– Drew, Commented Jul 22, 2015 at 5:23
@DrewPierce to find which one runs slow .. i need to get into logs , enable the slow query logs : set global slow _query_log ='ON' ,set global long_query_time = 1 ; but i cant get through /var/lib/mysql to see my logs .. it says permission denied . what to do ?? — prabuj prabha
– prabuj prabha, Commented Jul 22, 2015 at 6:12

georgecj11 · Accepted Answer · 2015-07-22 04:27:32Z

2

You can enable the general log and slow query logs.

Enabling general query log will log all the queries and might be heavy if you have many reads/writes. In slow query log, you can mention a threshold and only queries taking time beyond some time will be logged. Post that, you can manually analyze it or you can use tools provided( Percona has great tools)

answered Jul 22, 2015 at 4:27

georgecj11

1,64617 silver badges23 bronze badges

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

1 Comment

prabuj prabha Over a year ago

enable the slow query logs : set global slow _query_log ='ON' ,set global long_query_time = 1 ; but i cant get through /var/lib/mysql to see my logs .. it says permission denied . what to do ??

RadicalPumpkin · Accepted Answer · 2015-07-22 03:49:02Z

0

Have you analyzed your queries with explain plans? You may be able to find a query that will return the result set you wish that isn't as heavy a load on the query engine. Remember to only select the columns you actually need (try to avoid SELECT *), well planned indexing and to use inner/outer joins in favour of a huge list of WHERE clause filters.

Good luck.

RP

answered Jul 22, 2015 at 3:49

RadicalPumpkin

343 bronze badges

2 Comments

prabuj prabha Over a year ago

i want to know - how can i find my slow processing queries ? where it is located ?

prabuj prabha Over a year ago

enable the slow query logs : set global slow _query_log ='ON' ,set global long_query_time = 1 ; but i cant get through /var/lib/mysql to see my logs .. it says permission denied . what to do ??

Rick James · Accepted Answer · 2015-07-25 04:30:50Z

0

In addition to what the others said, use pt-query-digest (from percona.com) to summarize the slowlog. That will tell you the worst queries.

Performance tuning often involves knowing what indexes to put on tables. My index cookbook is one place to learn how to build an INDEX for a given SELECT.

answered Jul 25, 2015 at 4:30

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

SQL - Query Performance

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related