Hi.. I'm struggling with a query that I'm trying to simplify as much as
possible, but I can't seem to get rid of using the very same subqueries
several times.
Would there be a way to optimize the following so I get rid of
subqueries that do the exact same thing more than once?


SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
AS percentile FROM score AS s1
WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
vid = s1.vid
AND uid = (SELECT uid FROM users WHERE username = %s))

Moon's Father skrev:
> Just a look at your sql query at first.
> Your like key word's right must like this:
> like 's%', then it'll use the index you created for your table.
> The second try you may use temporary table to replace your own query like "
> (SELECT uid FROM users WHERE username = %s)
> "
>

Well, I am escaping the strings in sprintf in PHP, so the %s isn't an issue.
Can't it be done without temporary tables?



> On Dec 20, 2007 4:23 PM, Anders Norrbring <lists@norrbring.se
> <mailto:lists@norrbring.se>> wrote:
>
> Hi.. I'm struggling with a query that I'm trying to simplify as much as
> possible, but I can't seem to get rid of using the very same subqueries
> several times.
> Would there be a way to optimize the following so I get rid of
> subqueries that do the exact same thing more than once?
>
>
> SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
> WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
> AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
> AS percentile FROM score AS s1
> WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
> AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
> vid = s1.vid
> AND uid = (SELECT uid FROM users WHERE username = %s))
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=y...0608@gmail.com
>
>
>
>
> --
> I'm a mysql DBA in china.
> More about me just visit here:
> http://yueliangdao0608.cublog.cn

Brent Baisley skrev:
> You might be able to use variables to store the result of the query.
> Although I've never tried assigning the result of a query to a
> variable, only field values.
>
> SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
> WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s)
> AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s)
> ) * 100
> AS percentile
> WHERE s1.tid = @tid
> AND s1.vid = @vid
> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
> vid = s1.vid
> AND uid = (SELECT uid FROM users WHERE username = %s))
>
> Brent

At a first glance, it doesn't work at all, I get NULL results from it,
but I haven't spent any time trying to locate the problem yet..

So, I'm still open for ideas!



>
> On 12/20/07, Anders Norrbring <lists@norrbring.se> wrote:
>> Hi.. I'm struggling with a query that I'm trying to simplify as much as
>> possible, but I can't seem to get rid of using the very same subqueries
>> several times.
>> Would there be a way to optimize the following so I get rid of
>> subqueries that do the exact same thing more than once?
>>
>>
>> SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
>> WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
>> AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
>> AS percentile FROM score AS s1
>> WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
>> AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>> vid = s1.vid
>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>>
>>

Brent,
you put me on the right track.. Reading up a bit on syntax for
variables, I came up with the following, which seems to work.

SET @tid:=(SELECT tid FROM objects WHERE shortname = %s);
SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s);
SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
WHERE s2.tid = @tid AND s2.vid = @vid) * 100 AS percentile
WHERE s1.tid = @tid AND s1.vid = @vid
AND s1.highScore > (SELECT highScore FROM score WHERE tid = @tid AND
vid = @vid AND uid = (SELECT uid FROM users WHERE username = %s))

Setting the vars with SET before doing the SELECT seems to do it..

Anders


Anders Norrbring skrev:
> Brent Baisley skrev:
>> You might be able to use variables to store the result of the query.
>> Although I've never tried assigning the result of a query to a
>> variable, only field values.
>>
>> SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
>> WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s)
>> AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s)
>> ) * 100
>> AS percentile
>> WHERE s1.tid = @tid
>> AND s1.vid = @vid
>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>> vid = s1.vid
>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>
>> Brent
>
> At a first glance, it doesn't work at all, I get NULL results from it,
> but I haven't spent any time trying to locate the problem yet..
>
> So, I'm still open for ideas!
>
>
>
>>
>> On 12/20/07, Anders Norrbring <lists@norrbring.se> wrote:
>>> Hi.. I'm struggling with a query that I'm trying to simplify as much as
>>> possible, but I can't seem to get rid of using the very same subqueries
>>> several times.
>>> Would there be a way to optimize the following so I get rid of
>>> subqueries that do the exact same thing more than once?
>>>
>>>
>>> SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
>>> WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>> AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
>>> AS percentile FROM score AS s1
>>> WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>> AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
>>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>>> vid = s1.vid
>>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>>>
>>>
>
>
>

Brent Baisley skrev:
> You're right that wouldn't work because you can't assign the query
> results to a variable. You want to put the variables in the query and
> assign them to the value of the field.
> ...WHERE s2.tid = (SELECT @tid:=tid AS tid FROM objects WHERE shortname = %s)...
>
> That should work better, or at least put you on a better track.

I haven't checked this yet, but would it be better/smoother than putting
the variables assignments in separate SET statements in front of the
SELECT as I mentioned?

Anders


>
> On 12/20/07, Anders Norrbring <lists@norrbring.se> wrote:
>> Brent Baisley skrev:
>>> You might be able to use variables to store the result of the query.
>>> Although I've never tried assigning the result of a query to a
>>> variable, only field values.
>>>
>>> SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
>>> WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s)
>>> AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s)
>>> ) * 100
>>> AS percentile
>>> WHERE s1.tid = @tid
>>> AND s1.vid = @vid
>>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>>> vid = s1.vid
>>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>>
>>> Brent
>> At a first glance, it doesn't work at all, I get NULL results from it,
>> but I haven't spent any time trying to locate the problem yet..
>>
>> So, I'm still open for ideas!
>>
>>
>>
>>> On 12/20/07, Anders Norrbring <lists@norrbring.se> wrote:
>>>> Hi.. I'm struggling with a query that I'm trying to simplify as much as
>>>> possible, but I can't seem to get rid of using the very same subqueries
>>>> several times.
>>>> Would there be a way to optimize the following so I get rid of
>>>> subqueries that do the exact same thing more than once?
>>>>
>>>>
>>>> SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
>>>> WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>>> AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
>>>> AS percentile FROM score AS s1
>>>> WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>>> AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
>>>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>>>> vid = s1.vid
>>>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>>>>
>>>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>>
>>

You could use a view:

CREATE VIEW all_scores
SELECT s.tid, s.vid, s.uid, s.highScore
FROM score s
JOIN objects o
ON s.tid = o.tid
JOIN itemtypes it
ON s.vid = it.vid
JOIN users u
ON s.uid = u.uid
WHERE o.shortname = %s /* Should these ANDs really be ORs? */
AND i.itemtype LIKE %s;

SELECT highScore:= @my_high_score
FROM all_scores
WHERE u.username = %s
LIMIT 1;

SELECT COUNT(*):= @total_scores FROM all_scores;

SELECT COUNT(*):= @total_greater_my_score
FROM all_scores
WHERE highScore > @my_high_score;

SELECT ((@total_great_my_score + 1) / @total_scores) * 100 AS percentile;

Hope this s,

Jay


Anders Norrbring wrote:
> Brent Baisley skrev:
>> You might be able to use variables to store the result of the query.
>> Although I've never tried assigning the result of a query to a
>> variable, only field values.
>>
>> SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
>> WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s)
>> AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s)
>> ) * 100
>> AS percentile
>> WHERE s1.tid = @tid
>> AND s1.vid = @vid
>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>> vid = s1.vid
>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>
>> Brent
>
> At a first glance, it doesn't work at all, I get NULL results from it,
> but I haven't spent any time trying to locate the problem yet..
>
> So, I'm still open for ideas!
>
>
>
>>
>> On 12/20/07, Anders Norrbring <lists@norrbring.se> wrote:
>>> Hi.. I'm struggling with a query that I'm trying to simplify as much as
>>> possible, but I can't seem to get rid of using the very same subqueries
>>> several times.
>>> Would there be a way to optimize the following so I get rid of
>>> subqueries that do the exact same thing more than once?
>>>
>>>
>>> SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
>>> WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>> AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
>>> AS percentile FROM score AS s1
>>> WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>> AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
>>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>>> vid = s1.vid
>>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>>>
>>>
>
>
>

Anders Norrbring skrev:
> Brent,
> you put me on the right track.. Reading up a bit on syntax for
> variables, I came up with the following, which seems to work.
>
> SET @tid:=(SELECT tid FROM objects WHERE shortname = %s);
> SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s);
> SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
> WHERE s2.tid = @tid AND s2.vid = @vid) * 100 AS percentile
> WHERE s1.tid = @tid AND s1.vid = @vid
> AND s1.highScore > (SELECT highScore FROM score WHERE tid = @tid AND
> vid = @vid AND uid = (SELECT uid FROM users WHERE username = %s))
>
> Setting the vars with SET before doing the SELECT seems to do it..
>
> Anders

Hi..
I just want to take this one more round with you all..
Would the above be the easiest and most "slim" and efficient way to
build the query?
I'd rather avoid using views and temporary tables.

Anders.



>
>
> Anders Norrbring skrev:
>> Brent Baisley skrev:
>>> You might be able to use variables to store the result of the query.
>>> Although I've never tried assigning the result of a query to a
>>> variable, only field values.
>>>
>>> SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
>>> WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s)
>>> AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s)
>>> ) * 100
>>> AS percentile
>>> WHERE s1.tid = @tid
>>> AND s1.vid = @vid
>>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>>> vid = s1.vid
>>> AND uid = (SELECT uid FROM users WHERE username = %s))
>>>
>>> Brent
>>
>> At a first glance, it doesn't work at all, I get NULL results from it,
>> but I haven't spent any time trying to locate the problem yet..
>>
>> So, I'm still open for ideas!
>>
>>
>>
>>>
>>> On 12/20/07, Anders Norrbring <lists@norrbring.se> wrote:
>>>> Hi.. I'm struggling with a query that I'm trying to simplify as much as
>>>> possible, but I can't seem to get rid of using the very same subqueries
>>>> several times.
>>>> Would there be a way to optimize the following so I get rid of
>>>> subqueries that do the exact same thing more than once?
>>>>
>>>>
>>>> SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2
>>>> WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>>> AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100
>>>> AS percentile FROM score AS s1
>>>> WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s)
>>>> AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s)
>>>> AND s1.highScore > (SELECT highScore FROM score WHERE tid = s1.tid AND
>>>> vid = s1.vid
>>>> AND uid = (SELECT uid FROM users WHERE username = %s))