Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
How would you make this query better/faster?
code:
$sqlgetduplicate = "SELECT m.coordx, m.coordy, u.postcode, u.user_id
FROM USER_TABLE u, MAP TABLE m
WHERE m.name = '". $postcheckname . "'
AND m.coordx >= '".($rowurl['coordx']-4780)."'
AND m.coordx <= '".($rowurl['coordx']+4780)."'
AND m.coordy >= '".($rowurl['coordy']-7000)."'
AND m.coordy <= '".($rowurl['coordy']+7000)."'
AND u.postcode = m.post
AND u.username <> 'Anonymous'
ORDER BY u.username DESC";
im assuming using join or union, what would be the syntax?
[Edited on 12-12-2007 by Steve]
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
the bit slowing it down is this line
AND u.postcode = m.post
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
code:
$sqlgetduplicate = "SELECT m.coordx, m.coordy, u.postcode, u.user_id
FROM USER_TABLE u JOIN MAP TABLE m ON u.postcode = m.post
WHERE m.name = '". $postcheckname . "'
AND m.coordx >= '".($rowurl['coordx']-4780)."'
AND m.coordx <= '".($rowurl['coordx']+4780)."'
AND m.coordy >= '".($rowurl['coordy']-7000)."'
AND m.coordy <= '".($rowurl['coordy']+7000)."'
AND u.username <> 'Anonymous'
ORDER BY u.username DESC";
Dunno how much faster it will be though.
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
works but is as slow 
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Got any indexes on those tables?
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
yes the postcode on the map table is a primary index, there is a normal index on the postcode field in the users table
|
Gavin
Premium Member
Registered: 3rd Apr 02
Location: West Midlands
User status: Offline
|
throw more hardware at it... usually helps 
I'm more of an oracle man tbh
Other than indexing..... the "order by" would be fairly costly
pew pew pew pewwwww
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
nah as soon as i remove the line that compares the two tables its fine
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
with that line, the page processes in 1.3 seconds
without that line 0.07
|
Tim
Site Administrator
Registered: 21st Apr 00
User status: Offline
|
You are effectively joining in the first statement (just with a slightly more standard SQL syntax).
Indexes on u.postcode and m.post critical. They both exist?
Run the query with a prefix of 'EXPLAIN SELECT...' -- it will tell you exactly why it's slow. Watch for anything doing a file sort, table scan, or using temporary tables.
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
yeah tim field1 has a primary key index, field2 a normal index, will try that though ta
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
I would hazard a guess that the additional WHEREs are meaning the index can't be used.
An EXPLAIN will shed more light.
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
sorted, query for map now is down to 0.2 seconds or thereabouts
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
When are you making it fast? 
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
36 database queries in 0.5304830 seconds
taken from yours
[Edited on 13-12-2007 by Steve]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Yeah 36 queries, not one. 
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
thats just the one that was causing problems theres loads of other queries in there too
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
hold on il work out how many its doing
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
How did you sort it?
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
if theres a query inside a query does that query get multiplied how many times it takes the query its looping in to finish ?
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
A joined select is one.
Subselects inside brackets which run as dependent subqueries would be two or more.
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
21 queries altogether by my calculations
maybe my hardwares shit
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
Battle of the DB queries
i need a dedicated server 
i bet the one mines on is hosting 20 vbulletin forums all taking up valuable resources 
[Edited on 13-12-2007 by Steve]
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
quote:
Originally posted by Ian
How did you sort it?
rethought the way it finds duplicates, realised i didnt actually have to compare the two tables as the query it was already inside was generating a postcode on each pass, i just modified the query i posted in here to get all users from the users table which had the postcode it was generating on that pass, and filtered duplicates using GROUP BY on the usernames
[Edited on 13-12-2007 by Steve]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
GROUP BY won't always remove duplicates from the JOIN as it depends on what order they're done. Query optimiser should sort it out but you really need to have done an EXPLAIN before and after to see the exact affect of your changes.
|
