Czesc napisalem rest api w slim framework 3 i mam taki problem, gdy w api wywoluje zaptanie
SELECT * from ads;
api zwraca zapytanie w jsonie w czasie ok 60ms
w phpmyadmin wykonuje w 0.0014s
gdy ustalam docelowe zapytanie:
select distinct
ads.id,ads.user_id,(SELECT count(saved_ads.user_id) from saved_ads WHERE saved_ads.ad_id = ads.id AND saved_ads.user_id = ?) AS subscribe,us.name as userName,cat.name as categoryName,cit.name as cityName,ads.logo,ads.title,ads.description,ads.lat,ads.lon,ads.visits,ads.active,ads.created_at FROM ads INNER JOIN users as us on us.id=ads.user_id INNER JOIN categories as cat on cat.id = ads.category_id INNER JOIN cities as cit on cit.id=ads.city_id WHERE ads.active = '1' AND reviewed = '1' ORDER BY ads.created_at DESC LIMIT ? OFFSET ?;
to api zwraca wyniki w ok 120-150ms (w phpmyadmin Wykonanie zapytania trwało 0.0031 sekund)
w tabeli ads mam okolo 20 wierszy
w category ok 30
w cities ok 600
a w users 40
w jaki sposob zoptymalizowac zapytanie , zeby zejsc ponizej 100ms zapytania?