MySQL Forums
Forum List  »  Newbie

Re: Need help with mysql query
Posted by: Phillip Ward
Date: June 10, 2016 06:25AM

Quote

I am doing my best to learn mysql but I still have a lot to learn when queries involve loops or complex syntax.
As far as the database goes, stop thinking about "Loops" and start thinking about "Sets". Any query you construct will locate a Set of rows and return them to you.

If you then want to loop through those rows returned in your application code that's fine, but in the database, loops will not help you.

Complex syntax [all too] often means that your data is poorly structured and you're having to do "clever" things to work around that deficiency.

Quote

First question: how could I combine these two queries into one and get the total number of missing predictions for each user?
Possibly, but not easily, by which I mean the query will get more complex, which is a Bad Thing; not for MySQL, which will cope with practically anything, but will rapidly become unreadable and, therefore, unmaintainable.

Again, thinking about Sets of data rows, the first query returns a set of all games since the start of the season, the second a set of predictions made by a particular user. Different sets, different queries.
That said, you might be able to determine, within a Set of every game played, whether or not the user has made a prediction for that game, and to "total up" the result:

-- Air-Code Warning

select    count( g.id ) games 
,         count( p.userId ) predictions 
from       table-1-name g
left join  table-2-name p 
      on   g.id = p.gameId 
      and  p.userId = ? 
where      g.SeasonID = ? 
and        ( g.homeTeamScore > 0 or g.awayTeamScore > 0 ) 
order by   g.gametime desc

See what I mean about things getting complex?
Is that any easier to read than the two, simple queries that you had before? Not really. And, when you come back to this query after six months working on something else, clarity is really, really important.

Deciding what was "consecutive" with what would require a reference table listing all the possible week values, which you would join to the prediction entries made by the user. Your application code would then need to trawl through that Set and find the gaps; there's no easy way for MySql to do this because it has no concept of "previous" or "next" rows within a Set. You can do row-by-row processing within a Procedure but you should view this as a last resort. Looping procedure code creates single-threaded bottlenecks which mean poor application performance, and there's nothing you can do to "tune" these, short of re-writing them in a "better" way (which usually involves using the power of MySQL; simple SQL statements, working with Sets of rows).

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Need help with mysql query
June 10, 2016 06:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.