Page 2 of 2
Posted: Thu Mar 08, 2007 11:37 pm
by waqas_punjabian
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
Posted: Thu Mar 08, 2007 11:43 pm
by waqas_punjabian
Can you please tell me, from where i can download this 'Webinar'
Any related link ?
Posted: Fri Mar 09, 2007 2:28 am
by mikeq
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
Posted: Fri Mar 09, 2007 7:40 am
by waqas_punjabian
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.
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
------------------------------------------------------------
Thanks in advance,
regards
Waqas
Posted: Fri Mar 09, 2007 9:29 am
by mikeq
Have you had a bash yet?
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);
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
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);
I have not compared the efficiency of these as not at home, so might be worth running an explain against each.
Mike
Posted: Mon Mar 12, 2007 2:10 am
by waqas_punjabian
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
Posted: Mon Mar 12, 2007 2:32 am
by mikeq
welcome