My comment/question about optimization was VFP specific, Mike. Are you bathing again? <g,d&r>
I was also saying that using both distinct and group by in a query is potentially redundant, particularly with a single column query, and that all other things being equal a distinct must perform a table scan while a group may use an index. In VFP... <g> -- rk -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, April 16, 2014 2:02 PM To: [email protected] Subject: RE: SQL Query I've got a very large MariaDB (MySQL) table and ran an EXPLAIN on a GROUP BY query and a separate DISTINCT query on an indexed field. Both came back the same in terms of optimization. ymmv. On 2014-04-16 12:21, Richard Kaye wrote: > I was just throwing out some options that came off the top of my head. > There was no real thought put into optimizing beyond general rushmore > rules. > > Do you really need the DISTINCT when the GROUP BY is there? My > understanding, at least as far as VFP SQL is concerned, is that > DISTINCT will force a table scan whereas GROUP BY is rushmore > optimizable and will use an available index tag. > > And as Uncle Ted would say, none of this theoretical stuff matters. > You have to test with your data to know your stuff is optimized in > your environment. :-) [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD04422A746032E@ACKBWDDQH1.artfact.local ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
