speed up order by clause

[jemminger]

hi all,

i was working with some code last night, and one of my queries was a select statement joining two tables with an order by clause on two fields and a limit, e.g.

select
 foo,
 bar
from
 foos
 inner join bars
  on foos.id = bars.foos_id
where
 condition = met
order by
 foo,
 bar
limit 350, 50

there were around 150,000 records total in foo table, and this query was taking 13 seconds on average to execute. i built indexes on every field with no improvement.

then some thought occurred to me, and i tried

[b]
select *
from ([/b]
 select
  foo,
  bar
 from
  foos
  inner join bars
   on foos.id = bars.foos_id
 where
  condition = met[b]
) wrapper
order by
 foo,
 bar
limit 350, 50[/b]

lo and behold, it executed in about 2 seconds, and the order and offset still worked properly!

anyone see any potential problems with this method?

thought i'd share

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[TonyGroves]

Hmm ...

You'd expect the opposite effect; the multi-level query should take longer. But, as they say, the proof of the pudding is in the eating!

There's just one thing though; did you run each query alternately, a couple of times each? The second time a query is run (assuming the data hasn't changed in the meantime) is usually much faster, as queries and their results are cached by the server, and can be retrieved from memory instead of being processed repeatedly.

 

[jemminger]

yeah, i ran the first query probably ten times in a row, and was never better than 12 seconds.

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[chrissie1]

What ever gave you the idea to do that and think it would be better?

Sometimes I wonder if we are thinking the wrong way.

Amazing.

Christiaan Baes
Belgium

"Time for a new sig." - Me

 

[jemminger]

i think the genesis was that i just wanted a subset of the matching records (hence the limit) and i thought maybe i could get the unsorted subset then sort after on just that instead of first sorting all 65000 matching records.

turns out i had to move the limit outside too but it still worked in my favor.

guess i should go back to being a conformist, eh ;-)


-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big