a log of SQL statements that took more than `long_query_time` seconds to execute that are automatically saved to mysql.slow_query_log table or an output file
Questions tagged [mysql-slow-query-log]
196 questions
0
votes
1 answer
mysql-slow-log file is not generated
log_slow_queries = "C:\Program Files\MySQL\MySQL Server 5.1\mysql-slow.log"
long_query_time = 1
I wrote the lines above in my.ini file. But after running my query, which is taking more than 1 second to execute, the log file is not generated.…
PSR
- 39,804
- 41
- 111
- 151
0
votes
1 answer
MySQL optimization: more entries vs complex queries
I want to improve the speed of a notification board. It retrieves data from the event table.
At this moment the events MySQL table looks like this
id | event_type | who_added_id | date
In the event table I store one row with information regarding a…
olegb3
- 37
- 4
0
votes
1 answer
Query refuses to use index
Using MySQL version 5.5.28,
I have a table defined as such:
CREATE TABLE IF NOT EXISTS `groups` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(80) NOT NULL,
`desc` text NOT NULL,
`permissions` varchar(80) NOT NULL,
PRIMARY…
Tony Maro
- 1,854
- 17
- 14
0
votes
5 answers
how to optimize query to big table
I have a table with 18,310,298 records right now.
And next query
SELECT COUNT(obj_id) AS cnt
FROM
`common`.`logs`
WHERE
`event` = '11' AND
`obj_type` = '2' AND
`region` = 'us' AND
DATE(`date`) = DATE('20120213010502');
With next structure
CREATE…
user1016265
- 2,307
- 3
- 32
- 49
0
votes
1 answer
Using Percona pt-query-digest to analyse slow query log for a specific database only
Percona tool script, pt-query-digest, looks like it will be very useful to analyse a MySQL slow query log.
Does someone know how I can get the script to only look at queries relating to a specific database?
Thanks
iss42
- 2,720
- 3
- 21
- 37
0
votes
0 answers
Mysql query severe slow down with joins
have a table with approx 20,000 rows. Everything works fine but one of the queries in it has slowed down to about 5+ seconds. All of the tables have primary keys and their related indexes. There are also foreign keys and their related indexes. I…
jiraiya
- 977
- 4
- 13
- 34
0
votes
2 answers
Why do I have so many extension tables in my slow query log?
I want to optimize my mysql database and thus enabled the mysql slow query switch in my.cnf. What I see is many sys_log queries with many tables from my extension or others, too. Is this a slow query? How can I surpess it?
# Query_time: 0 …
Micromega
- 12,486
- 7
- 35
- 72
-1
votes
1 answer
mysql insert too slow and high io/cpu usage some time
the table row is about one hundred million, sometimes the io bps about 150
IOPS about 4k
os version: CentOS Linux 7
MySQL version: docker…
seqwait
- 159
- 1
- 7
-1
votes
1 answer
Slow queries contributing to high CPU consumption
These queries appear in our slow query log and we suspect these may be contributing to a high CPU consumption. Any insight on how to optimize them?
Query 1
Takes title of article and list of keywords and tries to match against similar entries
#…
master00
- 141
- 2
- 12
-1
votes
1 answer
activate MySQL slow query log in MySQL 5.5
After trying to activate MySQL slow query mode provided by the user manual (http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_slow_query_log_file) and other answers (How do I enable the MySQL slow query log?) , they all fail…
Chubaka
- 2,933
- 7
- 43
- 58
-1
votes
1 answer
Very slow Left Join on small/medium tables
I have an issue with a particular left join slowing down an important query drastically. Using PHPMyAdmin to test the query, it states that the query took 3.9 seconds to return 546 rows, though I had to wait 67 seconds to see the results which…
BZohar
- 33
- 3
-1
votes
1 answer
Slow Query Log returning wrong row examined
How the return number of rows this much, when there is only 20,000 rows i have in my database in given table. Please guide me in "row_examination". What does it mean?
CALL DB.sp_get_program(10, 0, '', '', '', '', 0, 0, 0, '', '',…
Amna
- 603
- 7
- 30
-2
votes
3 answers
Need a simple solutin to slow query
I have following query..
SELECT avg(h.price)
FROM `car_history_optimized` h
LEFT JOIN vin_data vd ON (concat(substr(h.vin,1,8),'_',substr(h.vin,10,3))=vd.prefix)
WHERE h.date >='2015-01-01'
AND h.date <='2015-04-01'
AND h.dealer_id <> 2389
AND…
imran
- 169
- 1
- 1
- 9
-2
votes
1 answer
How can i know the page from where slow query was hitting in MySQL in error log
How can i know the page from where slow query was hitting in MySQL, like
"SELECT * FROM Users WHERE id=123"
Lets say this query was written on page user.php and on many other pages and this query caught in slow log
Now i need to know the exact page…
Vikas Paliwal
- 11
- 2
-3
votes
1 answer
MYSQL Query Optimization with subqueries and joins
Hi I have 8 million row data and need to optimize Mysql query to fetch a row from that data. I am using below query but its server response time is too high that creating issue in page loading speed
SELECT q.id
, q.title
, q.question
…
Inderjeet Singh
- 17
- 4