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 ?
I created a sql fiddle for this http://sqlfiddle.com/#!2/7aad16/1 Is that pretty much right? Some immediate thoughts about speed 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)) ) Code (markup): Do you have the indexes set up like I've done them? Why are both category checks against a list? "category_id in(55)" and not an equals? I don't see the value in having the "distinct" in the query - that will just be slowing it down. Now, database design Why do both tables have category_id in them? Do they have a primary category and then a series of secondary categories? If you have control over that I'd remove category_id from the items table
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.
what sort of performance do you get from select items.* from items left join item_category on items.id= item_category. item_id where items.country_id=1 and (items.category_id=55 or item_category.category_id=55) group by items.id order by items.id Code (markup):
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.. .