i m having a issue with our directory site.
we have tables like listing, subcategory
Now what exactly we are looking for is to get list of subcategory group by their name and count as per the subcategory id matches in 2 fields of listing table
i.e. listing.subcategory and listing.keywords
listing.keywords have multiple subcategory ids and its separate by -
for ex. if subcategory name bar and restaurant id is 1131, we have to look for this id in listing.subcategory and listing.keywords
listing.keywords could be like -1109-1398-1131-
at the end i should get result like
id sub cat name count
1131 Bar & Restaurant 800
1245 chinese restaurant 45
I m now using the below sql query... and after a long time its working and giving me right result... but its taking around 4-5 seconds.. is it normal... because using this my site page loads slow...
Infact i m testing on xampp local server.
My listing table is having around 200,000 listings and subcategory table is having around 500
Query i use is
SELECT subcategory.id as id,subcategory.sub_cat_name as subcategoryname,count(*) as total FROM subcategory,listing WHERE listing.city = '40' AND listing.category = '92' and (listing.subcategory=subcategory.id or listing.keywords like concat('%-', subcategory.id, '-%')) AND listing.status='Active' group by subcategoryname ORDER BY subcategoryname ASC
please help me to make query fast... or any help