Ik heb twee queries die uitgevoerd worden voor een latest releases pagina op een muziek download site.

De queries zijn gebaseerd op 3 tabellen: tracks, albums en search_table.

De search_table bestaat uit de velden: track_id, album_id en release_date

De tabellen tracks en albums hebben alle twee een id en verdere info zoals: title, version, artist, label, release_date, etc... Daarnaast is de albums tabel gevuld met enkele duizenden albums en de tracks tabel met enkele tienduizenden tracks.

De queries zien er als volgt uit:

PHP Code:

$count = "
  SELECT COUNT(*) AS `numrows`
  FROM (`search_table`)
  LEFT JOIN `tracks` ON (`tracks`.`id` = `search_table`.`track_id` AND `tracks`.`enabled` = 1)
  LEFT JOIN `albums` ON (`albums`.`id` = `search_table`.`album_id` AND `albums`.`enabled` = 1)
  WHERE `release_date` <= 1308740400 ";

$select = "
  SELECT `tracks`.*, `albums`.*
  FROM (`search_table`)
  LEFT JOIN `tracks` ON (`tracks`.`id` = `search_table`.`track_id` AND `tracks`.`enabled` = 1)
  LEFT JOIN `albums` ON (`albums`.`id` = `search_table`.`album_id` AND `albums`.`enabled` = 1)
  WHERE `release_date` <= 1308740400
  ORDER BY `release_date` DESC
  LIMIT 0, 25 "; 


Het probleem is dat de count query er minimaal 1,5 seconde over doet terwijl de select query er maar 0,05 seconde over doet.

Is er iets waarmee ik de count query sneller kan maken? Het optimaliseren van de tabellen in PHPMyAdmin heb ik al gedaan en had helaas niet het gewenste resultaat.

Doe eens ipv count(*) count(`id`).
Geen idee of het uitmaakt maar valt te proberen :P

Hoeveel rijen staan er in. Het is logisch dat de 2de query sneller is omdat er een limit 0,25 in staat.

Samen iets meer dan 35.000.

Origineel gepost door Stefan Visser

Hoeveel rijen staan er in. Het is logisch dat de 2de query sneller is omdat er een limit 0,25 in staat.

Origineel gepost door Stefan Visser

Hoeveel rijen staan er in. Het is logisch dat de 2de query sneller is omdat er een limit 0,25 in staat.

Gosh ja, logisch dat hij een heel stuk sneller is :P

Dit geeft weinig tot geen verschil.

Origineel gepost door Wilbert E.

Doe eens ipv count(*) count(`id`).
Geen idee of het uitmaakt maar valt te proberen :P

Je zegt dat je database hebt geoptimimaliseerd, heb je indexes en foreign keys gemaakt(voor dat laatste moet je innoDB gaan gebruiken)? Probeer je trouwens het aantal searchresults, aantal albums of aantal tracks te tellen?

Voor de tabellen tracks en albums is er een primary key ingesteld op id.

Voor de tabel search_table zijn er indexen aangemaakt op track_id,album_id en release_date.

De type van alle tabellen staat op MyISAM.

De COUNT() is voor het totaal aantal tracks & albums, ook na een zoekopdracht naar bijvoorbeeld een artiest of label.

Origineel gepost door S.Voss

Je zegt dat je database hebt geoptimimaliseerd, heb je indexes en foreign keys gemaakt(voor dat laatste moet je innoDB gaan gebruiken)? Probeer je trouwens het aantal searchresults, aantal albums of aantal tracks te tellen?

Origineel gepost door Clifford James

Voor de tabellen tracks en albums is er een primary key ingesteld op id.

Voor de tabel search_table zijn er indexen aangemaakt op track_id,album_id en release_date.

De type van alle tabellen staat op MyISAM.

De COUNT() is voor het totaal aantal tracks & albums, ook na een zoekopdracht naar bijvoorbeeld een artiest of label.

Op zich zou een COUNT() van 35.000 rows geen laadtijd van 1,5sec mogen opleveren. Als je een simpele query als 'SELECT COUNT(*) FROM search_table' uitvoerd wat voor een laadtijd krijg je dan? Probleem zou kunnen zijn dat je verkeerde datatypes gebruikt zoals bigints waar normale ints gebruikt kunnen worden.

Verder lijkt me een preformance probleem dat je eerst joined en dan pas gaat selecteren via een WHERE clause:

Even een probeersel volgens mij is LEFT JOIN niet nodig gezien je altijd een album en track hebt?

Code:

SELECT COUNT(search_table.id) AS `numrows` 
  FROM search_table 
  WHERE search_table.release_date <= 1308740400 AND tracks.enabled =1 AND albums.enabled =1 AND  tracks.id = search_table.track_id AND search_table.album_id = albums.id

Ik weet niet zeker of deze query precies als antwoord geeft wat jij wilt maar misschien dat je daar zelf wat aan kunt finetunen. Hier kun je wat informatie vinden over left join optimalisation: http://dev.mysql.com/doc/refman/5.0/...imization.html

Succes ermee!

Een COUNT() zonder de LEFT JOIN is miliseconden werk, maar de query zoals jij hem opgeeft produceert een "Unknown column" error.

Origineel gepost door S.Voss

Op zich zou een COUNT() van 35.000 rows geen laadtijd van 1,5sec mogen opleveren. Als je een simpele query als 'SELECT COUNT(*) FROM search_table' uitvoerd wat voor een laadtijd krijg je dan? Probleem zou kunnen zijn dat je verkeerde datatypes gebruikt zoals bigints waar normale ints gebruikt kunnen worden.

Verder lijkt me een preformance probleem dat je eerst joined en dan pas gaat selecteren via een WHERE clause:

Even een probeersel volgens mij is LEFT JOIN niet nodig gezien je altijd een album en track hebt?

Code:

SELECT COUNT(search_table.id) AS `numrows` 
  FROM search_table 
  WHERE search_table.release_date <= 1308740400 AND tracks.enabled =1 AND albums.enabled =1 AND  tracks.id = search_table.track_id AND search_table.album_id = albums.id

Ik weet niet zeker of deze query precies als antwoord geeft wat jij wilt maar misschien dat je daar zelf wat aan kunt finetunen. Hier kun je wat informatie vinden over left join optimalisation: http://dev.mysql.com/doc/refman/5.0/...imization.html

Succes ermee!

Origineel gepost door Clifford James

Een COUNT() zonder de LEFT JOIN is miliseconden werk, maar de query zoals jij hem opgeeft produceert een "Unknown column" error.

Dan moet je even kijk welke collumn niet bestaat, heb je wel een search_table.id?

Ik had de query aangepast naar de tabellen. Maar hij kende de velden uit de tracks en albums database niet.

Origineel gepost door S.Voss

Dan moet je even kijk welke collumn niet bestaat, heb je wel een search_table.id?

misschien mysql_num_rows() gebruiken ipv count?

Origineel gepost door Clifford James

Ik had de query aangepast naar de tabellen. Maar hij kende de velden uit de tracks en albums database niet.

Fftjes albums en tracks toevoegen aan 'FROM search_table' werkt dan misschien..

Ik heb de COUNT() query maar opgesplitst in 2 aparte COUNT() queries, laadtijd is nu minder dan 0,1 seconde op z'n hoogst bij elkaar opgeteld. Hier kan ik enigszins mee leven.

Bedankt voor de hulp allemaal!