asked on

mysql query multiple tables count and group by

hi
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

Open in new window

momi_sabag

if you want to make your query faster try to split the keywords into a separate table
and have a single value in every row
this way you will join to another table instead of having an or in your join predicates
also, you will get rid of that horrible like which disable index usage

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tomas Helgi Johannsson

Hi!

Create an index like this on the table listing (change the name of the index as you please ;)

----
create index catcitystatus
using btree
on listing(status,city,category,subcatory,keywords);
-----
Also make sure that the subcategory table has similar index on these columns (id, sub_cat_name) .
This should give you some performance benefits on the query.

Regards,
Tomas Helgi

global_expert_advice

ASKER

thanks i understood and making things better now.