Innodb 900 000 rows duurt lang
Alleen als id voor een primary key, is hij het snelst.
Maar met indexen 10 keer langzamer.
Het werkt van een bepaald script, die aan ajax.php laad, waar vele ysql queries met left outer joins geladen worden.
Duur gemiddeld 40 sec om het geheel te laden.
MariaDB 10.1 word gebruikt.
Wie weer hoe ik dit supersnel kan maken?
Groet
Daniel
Geeft deze geen duidelijkheid waar de bottleneck zit?
SELECT count(*) as tot FROM `messages` WHERE `to` in (select profile_id from `user_profiles` where is_fake=1 and user_id = 1) and `_initiator` ='unanswered'
Die gaat dan door alle rijen heen om een nummer eruit te krijgen.
Dit duurt erg lang. Hoe kan dit sneller?
Toevoeging op 12/02/2018 19:29:44:
Update,
Ik weet nu welke query zo lang duurt:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT * ,b.profile_id as from_id , b.profile_name as from_name , b.profile_image as from_image, b.gender as from_gender , c.profile_id as to_id , c.profile_name as to_name , DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending FROM `messages` a LEFT JOIN user_profiles b ON a.from = b.profile_id LEFT JOIN user_profiles c ON a.`to` = c.profile_id WHERE `to` in (select profile_id from `user_profiles` where is_fake=1 and user_id=1) and `_initiator` ='unanswered' and hide = 0 order by `time_sending` desc LIMIT 5
$return = $db->query($sql );
while ($row = $return->fetch_object()){
/// de rest
}
$return = $db->query($sql );
while ($row = $return->fetch_object()){
/// de rest
}
duurt 30 sec
Hoe kan ik dit versnellen?
Bart V B op 12/02/2018 17:26:36:
Heb je ook al eens een EXPLAIN gedraaid op je query?
Geeft deze geen duidelijkheid waar de bottleneck zit?
Geeft deze geen duidelijkheid waar de bottleneck zit?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * ,
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
WHERE `to` in (select profile_id from `user_profiles` where is_fake=1 and user_id=1)
and `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
WHERE `to` in (select profile_id from `user_profiles` where is_fake=1 and user_id=1)
and `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
paar dingen:
aliases kun je gebruiken om niet steeds lange complexe tabelnamen te moeten typen. Of om duidelijkere namen te maken. Maar a, b en c draag alleen maar bij aan onduidellijkheid.
Verder is SELECT* in combinatie met een hele lijst kolommen die je dan nog apart op haalt, wat vreemd. Doe dan SELECT a.* of zo. (dit raad ik maar, omdat je wel uit b en c select met aparte kolommen.
En dan de subquery: WHERE iets IN ( een subquery) is doorgaans traag.
waarom niet een join?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a.* ,
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
LEFT JOIN user_profiles fake ON fake.profile_id = a.to and fake.is_fake=1 and fake.user_id = 1
WHERE `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
LEFT JOIN user_profiles fake ON fake.profile_id = a.to and fake.is_fake=1 and fake.user_id = 1
WHERE `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5
Ik zou alle kolomnamen prefix-en met de tabelnaam of alias. Dat maakt het voor je zelf duidelijker, uit welke tabel je iets haalt (en ook voor een ander)
Ik zie elder ook dergelijk queries met IN, maar deze krijg ik niet voor elkaar:
Code (php)
1
select * from messages a LEFT JOIN user_profiles b ON a.`to` = b.profile_id WHERE `to` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 ) and `from` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 ) and `hide` not in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
mijn mysql query skills zijn niet zo goed, dus snap niet hoe ik de NOT IN moet verbeteren
Toevoeging op 13/02/2018 09:25:02:
BEste Ivo,
De joins query, die haalt resultaten op en de initiele niet. Wat is hier anders aan?
Het is dus niet hetzelfde resultaat
Ik denk bij nader inzien, dat je misschien een join nodig had.
En mogelijk zou je die dan ook nog op de regel na FROM moeten/kunnen plaatsen.
Verder over je andere query:
probeer het eens met hier en daar een enter.
Verder lijkt me FROM niet zo'n heel handige kolomnaam, net als TO
https://dev.mysql.com/doc/refman/5.7/en/keywords.html#keywords-5-7-detailed-F
Om te beginnen:
een query van 300 tekens op 1 regel is niet leesbaar.
Als je dat verdeelt over meerdere regels, kom je een stuk verder.
Code (php)
1
2
3
4
5
6
2
3
4
5
6
select *
from messages a
LEFT JOIN user_profiles b ON a.`to` = b.profile_id
WHERE `to` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
and `from` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
and `hide` not in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
from messages a
LEFT JOIN user_profiles b ON a.`to` = b.profile_id
WHERE `to` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
and `from` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
and `hide` not in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
select *
from messages a
JOIN user_profiles naar
ON a.to = naar.profile_id AND naar.is_fake = 0
JOIN user_profiles van
ON a.from = van.profile_id AND van.is_fake = 0
LEFT JOIN user_profiles b ON a.`to` = b.profile_id
-- and `hide` not in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
from messages a
JOIN user_profiles naar
ON a.to = naar.profile_id AND naar.is_fake = 0
JOIN user_profiles van
ON a.from = van.profile_id AND van.is_fake = 0
LEFT JOIN user_profiles b ON a.`to` = b.profile_id
-- and `hide` not in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )
en die hide mag je zelf proberen