Search DaniWeb - clause

Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim …more efficient aggregate functions in your HAVING clause. Some aggregate functions might be more computationally…involve aggregations, including those using the HAVING clause. However, this approach comes with maintenance …suboptimal query performance, including queries involving the HAVING clause. > >Hardware Resources: Sometimes, improving… Re: Improve HAVING BY performance 1 Month Ago by Dani … AI missed one big one: When faced with a HAVING clause, before all else, see if you can convert it to… a WHERE clause, which is what I ended up being successful in doing… Re: Improve HAVING BY performance 1 Month Ago by Dani … that provoked this question, I was working on a HAVING clause that was filtering recommended topics by a bunch of OR… the time I was using a subquery in the SELECT clause, hence the need for HAVING. I switched to using JOINS… Re: Improve HAVING BY performance 1 Month Ago by Biiim … that provoked this question, I was working on a HAVING clause that was filtering recommended topics by a bunch of OR… the time I was using a subquery in the SELECT clause, hence the need for HAVING. I switched to using JOINS… Improve HAVING BY performance 1 Month Ago by Dani What are some ways to improve MySQL performance on queries against large tables that include the HAVING BY clause. It's my understanding that anything in there doesn't benefit from table indexes. Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim >see if you can convert it to a WHERE clause I made a foolish assumption that the HAVING was something … Re: Improve HAVING BY performance 1 Month Ago by Biiim …, but I wanted to make it known that the HAVING clause runs on the returned result set of your query, which… Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim I'm not very familiar with HAVING but my understanding is that it is used to filter results after a GROUP operation so I can't imagine that indexes would improve performance other than on the original SELECT. Using WHERE would return rows based on one or more criteria, and would benefit from indexing, but HAVING, as I understand, is performed after… Re: Improve HAVING BY performance 1 Month Ago by Dani Yes, that's true. But for those instances where we are left in circumstances where we have to perform HAVING on large tables, is there *anything* we can take into consideration for performance? Even if we must do large table scans, are there any tips in the most efficient way of doing them? Re: Improve HAVING BY performance 1 Month Ago by pritaeas Perhaps a smarter someone on this forum can help you look at the execution plan of your query and suggest some improvements. Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim Clearly you missed the part where my friend said, it's the same answer he would have given. In other words, the answer generated by ChatGPT was vetted by an expert. I do not understand your response in this case. If I were to post a code project here, and that project happened to contain a ChatGPT generated function, would that be a no-no in … Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim If I had taken the ChatGPT reply, reworded, then posted it, would that have been OK? Or would that be plagiarism? What if I posted my reworded version AND credited it as a rewording of a ChatGPT generated answer? Then that just makes me look like an idiot. Where is the line? Re: Improve HAVING BY performance 1 Month Ago by Dani > Clearly you missed the part where my friend said, it's the same answer he would have given. No, you didn't say it's the same answer he would have given. You said it's what he would have written *if he spent a lot of time researching.* There's a big difference between asking an industry expert for their personal opinion based on their own … Re: Improve HAVING BY performance 1 Month Ago by Dani I'll give you another example. I've been going to the doctor a lot lately. After each appointment, there is a long form summary of everything that was said, test results, interpretation of the test results, and next steps, all generally typed up by a medical student or resident. At the bottom it has my doctor's signature saying something along … Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim I asked someone who is an acknowledged expert (not just by me). He agreed with the generated answer. If I had instead written him that advice, letting him believe it was mine, he would have given the same response. In other words, it was most definitely not simply AI generated garbage. If I asked ChatGPT and my doctor what to do for a particular… Re: Improve HAVING BY performance 1 Month Ago by Dani What your expert friend would have written, had he been a forum member here, is different than what the AI wrote. Even if the overall sentiment is the same, the actual typed words coming from a human are different than those coming from an AI. While he *agreed* with the generated answer, it's not what he would have answered himself upon being … Re: Improve HAVING BY performance 1 Month Ago by Dani > Now you are just splitting hairs I don't think so, but let's just agree to disagree. Re: Improve HAVING BY performance 1 Month Ago by AndreRet And so the queen bee has spoken again, no matter the input or consideration from others. IMHO Reverend Jim were not out of line here, merely posted a scenario which could have helped... I truly saw/hoped for a turn around on the site but it seems to be governed by a "my way or the highway" mindset. Through some years I have really tried … Re: Improve HAVING BY performance 1 Month Ago by Dani I see it very differently than you see it. I consider Jim a real world friend of mine, as is his son, Adam, who I had actually invited to my wedding. I didn't bash Jim with my moderator nor admin nor forum owner hat on. He contributed a post where I disagree with his viewpoint. He disagrees with mine. We had a public debate about it. I took … Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim >We "bashed" viewpoints, and never each other As it should be. I wish this applied to the real world on a broader scale (we all know who I am talking about). >I would hope that's the way Jim took it too Absolutely. Dani is a respected friend and I always appreciate her viewpoint whether I agree or disagree with it. And I … Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim Was it worth all that for 50ms? Re: Improve HAVING BY performance 1 Month Ago by Dani In exchange for less than a day's worth of work? Of course!!!!! I've spent a *lot* longer to shave off a *lot* less. https://www.conductor.com/academy/page-speed-resources/faq/amazon-page-speed-study/ Re: Improve HAVING BY performance 1 Month Ago by Dani Now as far as whether going down that rabbit hole that day was worth the cost of losing AndreRet, then I'd have to give a resounding no. Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim I have to admit that 95% of my work from 1995 to 2008 was back end stuff where I didn't have to worry about stuff like that. Digital plumbing and monitoring. The other 5% was single user apps. Not counting the 20% which was pointless meetings. So if you don't mind explaining, I'm curious as to why 50ms would even be noticible. I'm not asking just … Re: Improve HAVING BY performance 1 Month Ago by Dani I'm too exhausted for an in depth explanation right now but 90% of web development is optimizing for performance. The average DaniWeb page takes anywhere from 80ms up to 250ms to load the HTML (when dealing with low network latency), depending on the type of page, so shaving 50ms off of that is a huge win. Re: Improve HAVING BY performance 1 Month Ago by Dani Here are some articles that can explain it in greater depth: * https://web.dev/learn/performance/why-speed-matters * https://developer.mozilla.org/en-US/docs/Learn/Performance * https://web.dev/explore/fast The HTML page must be downloaded in its entirety before the web browser can begin loading anything else (CSS, Javascript, images, etc.)… Re: Improve HAVING BY performance 1 Month Ago by Reverend Jim I suppose I am looking at it in terms of practicality. I suppose there might be several reasons to optimize: 1. It saves resources on the hosting platform 2. It improves the user experience 3. It provides personl satisfaction Point #1 would save you money if the savings were significant. Point #2 would apply if the improvement was … Re: Improve HAVING BY performance 1 Month Ago by Dani > Point #2 would apply if the improvement was noticible but I doubt anyone could seriously comment, "I think this page rendered 50ms faster than it used to", especially considering all the other things that affect timing, for example, the current loading on my ISP servers, anything else running on my computer or home network, etc. … Re: Improve HAVING BY performance 1 Month Ago by Dani > I know that in my experience, OR's are very bad for speed you can get away with a few but they can get very bad when they stop the whole query making use of one of the main indexes, you can actually speed it up by moving the or's into the HAVING usually. Thank you for that tip! I never thought of that, but it totally makes sense!! > … Re: Improve HAVING BY performance 1 Month Ago by toneewa I'm a little late to the party, however, I want to share my experience learning MySQL in the past day. I setup a server, a database, and wrote a C++ program to connect to it. It measures the times for 3 SELECTs. The whole product list, using HAVING, and WHERE. I also tested reversing the orders. Query execution time: 0.0002336 seconds …