Re: Online 7.11 Select Performance - Odd Results

On 29 Nov 1995, Clay Irving wrote: <<<< Stuff deleted >>>> > This is the problem: > table_x (30 columns, 22000 rows), index on (column1,field2,field3) > > SELECT * FROM table_x ORDER BY 1,2,3 (works fine)> > SELECT * FROM table_x ORDER BY 1 (works fine)> > SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time)> > SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time)> > SELECT column1,column, column3 ORDER BY 3 (works fine)> > What do I mean by "*EXTREMELY* long time? On a dedicated 4-processor > HP K400, the query completes in approximately 10 minutes! > > - o - o Clay Irving (clay@panix.com), N2VKG > o - o o AEC NYC ARES, New York County > o - Deputy Radio Operator, NYC RACES, New York County > - o - - Start --> http://www.panix.com/clay Clay, My experience, too. When you use the last part of a concatenated key, i.e. 3 of index(1,2,3), the performance is a doggie. So things hum when you use all three, or the first two, or the first one, and I suspect you're getting good results when you select on all three columns for the same reason, but to get a clear picture of what's happening, set explain on; select .... , etc. then read the explain.out diagnostic file in the current directory. Off the top of my head, y'all forgive me for shooting my bandwith off, a kluge might be select x from your_table where col1 is not null (if it's key it can't be, but so what) and col2 is not null and col3 = something; Anyway, don't give up. There's usually a way. Yours, Nick expedient was