Innodb 900 000 rows duurt lang

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Daniel van Seggelen

Daniel van Seggelen

12/02/2018 17:18:07
Quote Anchor link
Ik heb een tabel "berichten" met 900 000 rows. de tekst veld daarvan waar tekst in staat heb ik geindexed, een gewone index en een fullsearch index geprobeert.
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
 
PHP hulp

PHP hulp

17/12/2018 21:45:38
 
Bart V B

Bart V B

12/02/2018 17:26:36
Quote Anchor link
Heb je ook al eens een EXPLAIN gedraaid op je query?
Geeft deze geen duidelijkheid waar de bottleneck zit?
 
Daniel van Seggelen

Daniel van Seggelen

12/02/2018 18:52:55
Quote Anchor link
Ik zie hier de query die nu geladen word:

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)
PHP script in nieuw venster Selecteer het PHP script
1
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
}


duurt 30 sec
Hoe kan ik dit versnellen?
 
Thomas van den Heuvel

Thomas van den Heuvel

12/02/2018 20:20:44
Quote Anchor link
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?
 
Ivo P

Ivo P

12/02/2018 21:51:31
Quote Anchor link
het is op zich niet verboden om leesbare query's te schrijven.
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


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)
PHP script in nieuw venster Selecteer het PHP script
1
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



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)
 
Daniel van Seggelen

Daniel van Seggelen

13/02/2018 08:31:49
Quote Anchor link
Bedankt werkt een stuk sneller.

Ik zie elder ook dergelijk queries met IN, maar deze krijg ik niet voor elkaar:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
 
Ivo P

Ivo P

14/02/2018 15:05:17
Quote Anchor link
ik heb een left-join gebruikt.
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)
PHP script in nieuw venster Selecteer het PHP script
1
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 )



Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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 )


en die hide mag je zelf proberen
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.