Thanks Volka,
So, now i have only one index on this table that is on ProductID.
And thanks mikeq,
For all of your posts and your optimized query,
it really express better results. like: 0.0011 second.
Thanks to all of you for this great and informative discussion.
Regards,
Waqas
[Solved]Query to find within Group
Moderator: General Moderators
-
waqas_punjabian
- Forum Commoner
- Posts: 67
- Joined: Wed Aug 10, 2005 9:53 am
http://www.mysql.com/news-and-events/on ... -webinars/
You need to register, which is free to do. You will also need to download the player software, just read the instructions on the site.
Mike
You need to register, which is free to do. You will also need to download the player software, just read the instructions on the site.
Mike
-
waqas_punjabian
- Forum Commoner
- Posts: 67
- Joined: Wed Aug 10, 2005 9:53 am
Thanks Mike,
Here i need another Query, If you help me i will be really thanking to you.
I need a query which will select those records/groups which have PrimaryFlag='y' in more than one records, for example look at ProductID 2 group.
The result should be like below.
Thanks in advance,
regards
Waqas
Here i need another Query, If you help me i will be really thanking to you.
I need a query which will select those records/groups which have PrimaryFlag='y' in more than one records, for example look at ProductID 2 group.
Code: Select all
tbl_images
------------------------------------------------------------
imgID ProductID ImageFile PrimaryFlag
------------------------------------------------------------
1 1 a n
2 1 b n
3 1 c y
4 2 d n
5 2 e y
6 2 f y
7 3 g y
8 3 h n
9 3 i y
11 4 g y
12 4 h n
---------------------------------------------------The result should be like below.
Code: Select all
------------------------------------------------------------
imgID ProductID ImageFile PrimaryFlag
------------------------------------------------------------
4 2 d n
5 2 e y
6 2 f y
7 3 g y
8 3 h n
9 3 i y
------------------------------------------------------------regards
Waqas
Have you had a bash yet?
This would work
Problem with that one is that I am not sure if the sub-query is valid SQL standard. I dont know off hand if you have include the COUNT(t2.productid) in the SELECT field list. Although it works if not standards compliant it may break in the future
Another option is
I have not compared the efficiency of these as not at home, so might be worth running an explain against each.
Mike
This would work
Code: Select all
SELECT
t1.*
FROM
`tbl_images` AS t1
WHERE
t1.productid IN
(SELECT t2.productid
FROM tbl_images AS t2
WHERE t2.primaryflag = 'y'
GROUP BY t2.productid
HAVING COUNT(t2.productid) > 1);
Another option is
Code: Select all
SELECT
t1.*
FROM
`tbl_images` AS t1
INNER JOIN (SELECT t2.productid, COUNT(t2.productid)
FROM tbl_images AS t2
WHERE t2.primaryflag = 'y'
GROUP BY t2.productid
HAVING COUNT(t2.productid) > 1) AS t3
ON (t1.productid = t3.productid);
Mike
-
waqas_punjabian
- Forum Commoner
- Posts: 67
- Joined: Wed Aug 10, 2005 9:53 am
Thanks Mike
The later Query (i.e, with join)
that work fine and it's faster than the previous one.
The results were as follows:
with join query: 0.0059 sec
with other query: 5.4566 sec
a hell of difference
This query helped me a lot and shortened my work up to 1/3.
Well, thanks a lot for your help & kind response.
Regards,
Waqas
The later Query (i.e, with join)
that work fine and it's faster than the previous one.
The results were as follows:
with join query: 0.0059 sec
with other query: 5.4566 sec
a hell of difference
This query helped me a lot and shortened my work up to 1/3.
Well, thanks a lot for your help & kind response.
Regards,
Waqas