Mysql Query Issue

Hi Guys

I am stuck and the Query i have is killing my mysql resources. here is the case
I have 2 tables

items
------------
id ( primary and AI )
heading varchar ( 255)
category_id ( 1 to 82)
country_id ( 1/2/3)
status ( 1/0)

Second table is
item_categories
------------
id ( primary and AI )
item_id ( foreign key for item.id)
category_id ( 1 to 82)

Here is the issue

The working Query, that takes a lot of resources :
select * from items where country_id=1 and (category_id in(55) or id in(select distinct(item_id) from item_category where category_id in (55)))

This gets me items which have category (55)

I have tried writing this query multiple way with inner joins and joins etc. but number of result is not correct

Couple of Wrong examples
a)
select a.* from items a join item_category b on(a.id=b.listing_id)
and a.country_id=1 and a.category_id=55 and b.category_id=55
b)
select a.* from items a join item_category b on(a.id=b.listing_id)
and (a.country_id=1 and a.category_id=55) or (b.category_id=55)

Any suggestions ?

 

Hi Sarah K

Yes the SQL Fiddle have current right sample data, ( good tool , will usethis next time I ask for a SQL query )

Regarding Database Table design . Yes
Item needs to have first and mandatory category_id, that is item.category_id , some items may have multiple categories and some may not.
Some may have 1 or 2 and some may go up to 5 , that why created second item_category table.

Relation between item and item_category is 1 to many.
Obviously to item_category table can have multiple rows for the same item_id with different category_id

the code you have written is what I have , but that takes ages, up to 18 seconds.

Here is the answer to your questions.
Do you have the indexes set up like I've done them?
Answer : Yes, I tried that and changing storage engines etc too.

Why are both category checks against a list? "category_id in(55)" and not an equals?
Answer : sometimes user can select multiple category_id or it could be category_id in(55,44) , that the reason for that.
I tried both commands with category_id in() and category_id= , result time is very similar ( 0.0192 and 0.0205 )

I don't see the value in having the "distinct" in the query - that will just be slowing it down.
Answer : If I don't add distinct It can return multiple of the same listings id again and again, once we use the multiple category selection like
category_id in(44,55)

Hope this helps, Is their any other way to use Joins for this...

Thanks for your time...

Manish

 

I did some changes to the records and added some data to it, still may be you are right the distinct doesn't have to be there.

Thought with or without the time difference it very small.
I think I found the issue too , cause I am using

select * from view_items where ***

and then doing a sub query on top, that is taking way too long, If I do a simple straight Query that is a lot better.

Thanks for your help.... does help me resolve the issue.

 

here is the answer
Case 1 : Using Connections between 2 tables
Using sub query goes in : 0.0159
Using the Left Join Goes in : 0.0818 to 0.0973
Note : the number of rows came back are not same, need to double check )

Case 2 : Replacing item table with a bulky view
using subquery : Hangs the Server
Using that with Left Join : 0.0937 ( THIS IS DEFINITELY A WINNER )


thanks Heaps for your help.

I have replace the search with Case 1 Option 1,
Only drawback is some other tables in that were connected in bulky view have to be fetch again and again as the result page each item goes. )
This is a test if things work out with this option then good, Or Else will move to Option 2 and Case 2

Want to know if you do some freelancing work, Would rather use your skills to sort out all the queries and view may improve the website loading and functioning time.. .