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 <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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/[EMAIL PROTECTED]
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
