Keiter: Dimi1947: can we help you?

Averill: Hi everyone. I’m an intermediate user looking for some guidance about a particular optimization and general feedback about everything from table names to column data types.

Gozman: Rather than give you a bunch of ***ue details I’m working on a paste with comprehensive information so you can get the full picture

Hilado: Http://pastebin.com/eEDpaZe5

Guyet: Danfield: “Hi my query is slow. can you make it faster? kthx”

Danfield: Lucid: This is loosely true: For planning – competent hardware, competent mysql server config as to buffers and caches and log file sizes, normalized schema design, correct table storage engine choice, proper data types, adequate indexing, competent queries. For performance troubleshooting – reverse the order. :-

Edem: Danfield: really interesting and I can’t argue. never heard that before!

Fili: Danfield: btw I absolutely don’t discount my failing on the last first, in the latter case point

Capizzi: I recently worked on a site that was slow. Converted to InnoDB and set buffer pool to just over the site of the dataset – omg so fast

Mogan: Danfield: query competency is a big issue for me because I don’t have a good mental model of how queries are optimized, how joins work, etc

Defouw: You can see my incompetence at the end of the paste :

Danfield: Not me, I’ve got to go mow the lawn. Good luck. :-

Sothman: Danfield: I’d rather deal with slow queries, have fun ;

Danfield: You got that right :-/

Castera: I’ve got to do mine too but I’m going to wait until it isn’t the hottest part of the day

Correl: Lucid: By the way SHOW CREATE TABLE is way much better than DESCRIBE and SHOW INDEXES

Correl: Lucid: As for the query at the end of pastebin it makes zero sense to me so I’d rewrite it from scratch

Correl: Lucid: SELECT * . GROUP BY .; never makes any sense

Calcagni: Salle: so when I am tempted to do that. what do I do instead?

Pfahlert: Lucid: Most implementations of sql don’t allow the mixing of grouped and ungrouped columns in the SELECT clause. mysql does, but the row selected for the ungrouped column is an unpredictable value. The groupwise max solution is a way to specify the value selected for the ungrouped column.

Branning: Lucid: http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

Correl: Lucid: If you are tempted to do that you are doing somethnig wrong

Correl: Keiter: not even groupwise max in his case

Keiter: Salle: I can’t look at his link, I’m just throwing it for good measure.

Correl: Lucid: Not even mentioning how bad dependent subqueries are for performance in most cases. Usually these can be rewritten using derived tables

Correl: Maybe not in this particular case though

El: I feel more novice by the second

Assis: Its okay though this is good

Corcuera: Frankly I don’t know what derived tables are can make an inference based upon the name but don’t know the performance implications or usage

Sario: I need a minute to read about some things

Correl: Lucid: With SELECT * . GROUP BY .; you select something unpredictable

Kutzner: Salle: true and that’s a big problem for me when I do it

Correl: Lucid: Set SQL_MODE flag ONLY_FULL_GROUP_BY and MySQL will not allow you do such mistake

Dondlinger: Salle: but I don’t know how to get some aggregate value from of subsets of the data

Barfknecht: Salle: once I get this query fixed I will do that

Correl: Lucid: You will do what? :

Correl: Lucid: You can’t fix query which makes no sense you know

Dietsch: Salle: well I can’t set SQL_MODE to ONLY_FULL_GROUP_BY and break everything

Shadowens: Salle: so I need to rewrite the query and then I can do that