corsasport.co.uk
 

Corsa Sport » Message Board » Off Day » Geek Day » SQL JOIN, UNION?


New Topic

New Poll
  <<  1    2  >> Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author SQL JOIN, UNION?
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
12th Dec 07 at 17:27   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
12th Dec 07 at 17:51   View Garage View User's Profile U2U Member Reply With Quote

the bit slowing it down is this line

AND u.postcode = m.post
James
Member

Registered: 1st Jun 02
Location: Surrey
User status: Offline
12th Dec 07 at 17:54   View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
12th Dec 07 at 18:19   View Garage View User's Profile U2U Member Reply With Quote

works but is as slow
James
Member

Registered: 1st Jun 02
Location: Surrey
User status: Offline
12th Dec 07 at 18:35   View User's Profile U2U Member Reply With Quote

Got any indexes on those tables?
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
12th Dec 07 at 18:43   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 3rd Apr 02
Location: West Midlands
User status: Offline
12th Dec 07 at 18:47   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
12th Dec 07 at 18:50   View Garage View User's Profile U2U Member Reply With Quote

nah as soon as i remove the line that compares the two tables its fine
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
12th Dec 07 at 18:52   View Garage View User's Profile U2U Member Reply With Quote

with that line, the page processes in 1.3 seconds

without that line 0.07
Tim
Site Administrator

Avatar

Registered: 21st Apr 00
User status: Offline
12th Dec 07 at 21:50   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
12th Dec 07 at 21:58   View Garage View User's Profile U2U Member Reply With Quote

yeah tim field1 has a primary key index, field2 a normal index, will try that though ta
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
13th Dec 07 at 01:03   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 14:48   View Garage View User's Profile U2U Member Reply With Quote

sorted, query for map now is down to 0.2 seconds or thereabouts
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
13th Dec 07 at 14:58   View Garage View User's Profile U2U Member Reply With Quote

When are you making it fast?
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 15:03   View Garage View User's Profile U2U Member Reply With Quote

36 database queries in 0.5304830 seconds



taken from yours

[Edited on 13-12-2007 by Steve]
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
13th Dec 07 at 15:09   View Garage View User's Profile U2U Member Reply With Quote

Yeah 36 queries, not one.
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 15:10   View Garage View User's Profile U2U Member Reply With Quote

thats just the one that was causing problems theres loads of other queries in there too
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 15:10   View Garage View User's Profile U2U Member Reply With Quote

hold on il work out how many its doing
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
13th Dec 07 at 15:11   View Garage View User's Profile U2U Member Reply With Quote

How did you sort it?
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 15:11   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
13th Dec 07 at 15:12   View Garage View User's Profile U2U Member Reply With Quote

A joined select is one.

Subselects inside brackets which run as dependent subqueries would be two or more.
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 15:13   View Garage View User's Profile U2U Member Reply With Quote

21 queries altogether by my calculations

maybe my hardwares shit
Steve
Premium Member

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 15:18   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
13th Dec 07 at 15:22   View Garage View User's Profile U2U Member Reply With Quote

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

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
13th Dec 07 at 15:33   View Garage View User's Profile U2U Member Reply With Quote

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.

  <<  1    2  >>
New Topic

New Poll

  Related Threads Author Forum Replies Views Last Post
Need a good MySQL host Matt H Geek Day 3 969
14th Feb 05 at 22:08
by John
 
SQL Server Bart Geek Day 1 790
1st Feb 07 at 16:44
by Samls
 
sql/forms builder help - pretty basic really Tantastic Geek Day 6 645
28th Mar 07 at 18:06
by Tantastic
 
XML/PHP Laney Geek Day 6 757
26th Apr 07 at 15:03
by Ian
 
FAO: Programmers barteh Geek Day 6 741
6th Nov 07 at 14:02
by barteh
 

Corsa Sport » Message Board » Off Day » Geek Day » SQL JOIN, UNION? 29 database queries in 0.1691658 seconds