Solved

mysql query multiple tables count and group by

Posted on 2012-04-13
4
515 Views
Last Modified: 2012-04-16
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

0
Comment
Question by:global_expert_advice
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
Comment Utility
@momi_sabag said it well.  Having data stored like that and using LIKE '% ... %' is very bad design.  You should seriously consider restructuring your database..... no I take that back.... You MUST restructure your database.   But for now I can suggest some ways to improve this:

TEMPORARY FIXES:
1.  Ensure you have indexes on the relevant columns.  A concatenated index on the columns of listing mentioned in your WHERE clause (except keywords) might help.  Learn how to use EXPLAIN (http://dev.mysql.com/doc/refman/5.1/en/using-explain.html) in order to help you with this.

2.  Avoid count(*).  Best to use count(primary_key_column), so maybe count(listing.id) ?


POSSIBLE ALTERNATIVE LONG TERM SUGGESTIONS:

3.  If your tables are myisam, consider changing how data is stored in your keywords column .  Store words "normally", meaning separated by spaces, and store numbers with something prefixed to it, like num_111.  Then you can create a  full-text index on that column and use "match(listing.keyword) against ('num_111').  At least this way it uses an index.

4.  Cases like this I would actually go against having to count every time if I can.  I would do something like:

create table category_counts as
SELECT subcategory.id as subcategory_id, 
             subcategory.sub_cat_name as subcategoryname, 
             listing.city,
             listing.category,
             count(listing.id) as total  
FROM subcategory,listing 
WHERE  (listing.subcategory=subcategory.id or listing.keywords like concat('%-', subcategory.id, '-%')) 
AND listing.status='Active' 
group by subcategory.id, subcategoryname, listing.city, listing.category;

alter table category_counts add index (city, category);

Open in new window


Then I will modify the application code so that whenever a listing is added/deleted/modified, the appropriate rows in category_counts are updated (incremented, decremented, added).  This way to get subcategories for a given category and city, you only need to do:

select * from category_counts where city=40 and category=92;

Open in new window


Note that even if you do #4 I still seriously recommend you get rid of that '-11-22-33-44-' data storage method.  

Hope this helps and good luck.
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
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
0
 
LVL 1

Author Closing Comment

by:global_expert_advice
Comment Utility
thanks i understood and making things better now.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now