All Questions
35
questions
0
votes
0
answers
46
views
Mysql select with WHERE (a, b) in (('str1', 'str2'), ...., ('str6000', 'str6000')) taking 30 seconds to execute
I have table of the following structure:
create table my_table
(
id int auto_increment
primary key,
name varchar(255) null,
vendor_name ...
1
vote
2
answers
398
views
Nested loop inner join with index lookup and filter is slow
I have this query I'm running in MySQL:
SELECT
count(*)
FROM
library AS l
JOIN plays AS p ON p.user_id = l.user_id AND
l.path = p.path
WHERE
l.user_id = 20977 AND
p.time >= '...
- 8,140
0
votes
1
answer
111
views
Using LIMIT makes query much slower - optimiser moves ORDER BY
I have the following query, which runs slowly:
SELECT l.track, r.rating
FROM library l LEFT JOIN rating r ON r.type='song' AND r.id=26452 AND r.value=CONCAT(l.name, ':', l.path)
WHERE l.id=26452
...
- 8,140
0
votes
3
answers
372
views
How to improve slow query performance?
I have a multi-join query that targeting the hospital's chart database.
this takes 5~10 seconds or more.
This is the visual expain using mysql workbench.
The query is below.
select sc.CLIENT_ID as '...
- 163
0
votes
1
answer
61
views
Mysql Query Optimization for this particular query
I am having a mysql query which is taking roughly 2.6s to execute. At first it was not using index. Now I added index to it. But still no improvements. Any suggestion on this:
select posummary0_.id ...
-1
votes
1
answer
33
views
How can I optimize DB query?
My db query is taking 5 seconds instead of <1 second earlier and the culprit query is :
explain extended
select A,B,C
from table flow
where
(status in ('X' , 'Y' , 'Z'))
and priority=1
and ...
- 433
1
vote
0
answers
82
views
Why mysql is not using index on join?
I have the two tables named A and B. My query for explain SQL is:
explain select * from A k join P p1 on p1.aggregate_id=k.aggregate_id where p1.start=1 and p1.id= 123 \G;
*************************** ...
- 433
0
votes
1
answer
99
views
When mysql updates a non-indexed column，why can use the primary key
MySQL version is 8.0.23
Create table and insert data
CREATE TABLE `test_update` (
`id` int NOT NULL ,
`column1` varchar(20) DEFAULT NULL,
`column2` varchar(20) DEFAULT NULL,
...
- 1
7
votes
1
answer
272
views
MySQL Optimiser - cost planner doesn't know when DuplicateWeedout Strategy creates disk table
This is my sample query
Select table1.id
from table1
where table.id in (select table2.id
from table2
where table2.id in (select table3.id
...
- 1,294
0
votes
0
answers
54
views
MySQL Common price from previous 10 days vs current price - MySQL Performance Improvement
I am casually building a website using phpMyAdmin and have created the below SQL code to get the most frequent price from the previous 10 days and compare it to the current price to identify price ...
- 1
0
votes
0
answers
31
views
dependant subquery processed despite Extra Impossible WHERE noticed after reading const tables
in this query https://www.db-fiddle.com/f/snzSLd8y1oRNRqdsFpNdHa/0
SELECT test0.id,
(SELECT GROUP_CONCAT(test2.one_info) FROM test2 WHERE test2.one_id = test1.id) AS GC
FROM test0
INNER JOIN test1 ON ...
- 543
2
votes
4
answers
2k
views
Slow Query Groupby, Join MYSQL laravel
I have 4 tables: places, brands, categories, locations.
The relationship are Places belongTo Brands and Places ManytoMany with Categories and Locations.
I want to get search results Places with ...
- 2,348
0
votes
2
answers
69
views
MySQL chooses to execute queries, or not, at whim
For a reporting output, I used to DROP and recreate a table 'mis.pr_approval_time'. but now I just TRUNCATE it.
After populating the above table with data, I run an UPDATE statement, but I have ...
- 2,275
0
votes
1
answer
2k
views
Using IN with a subquery doesn't use index
I have the fowlloing query
select * from mapping_channel_fqdn_virtual_host where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
explaining the above query gives me the following result:
explain select * ...
- 8,001
1
vote
2
answers
499
views
Why isn't MySql using the index on our table?
We're using MySql 5.5.37, Java 7 and Hibernate 5.1.3. Hibernate is auto-generating queries and there is one that is confusing me. We have these tables ...
CREATE TABLE `user` (
`ID` varchar(32) ...
- 17.3k