Solved

mysql query multiple tables count and group by

Posted on 2012-04-13
4
555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37844670
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
ID: 37845383
@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 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37849204
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
ID: 37851278
thanks i understood and making things better now.
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

752 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