StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your query should be returning a count for all jobs for all employers, but there are some criteria that they must match to show up. An implicit join, as you are using, is an INNER JOIN. This requires all criteria to match in order for the rows to be included. This means it only returns jobs that are "isActive", "beenActive", "status=1", and createdAt is less than 30 days in the past, where employeres are "isActive" and "status=1". Check your data to see if this is what you want.</p> <pre><code>SELECT c.id AS employerID, count(*) as total FROM employer as c, job as j WHERE c.isActive=1 AND c.status=1 AND j.employerIDFK = c.id AND j.isActive=1 AND j.beenActive=1 AND j.status=1 AND DATE_ADD( j.createdAt, INTERVAL 30 DAY ) > NOW() GROUP BY c.id </code></pre> <p>As far as what others are trying to say regarding the filter on the createdAt, MySQL will first evaluate NOW() (just once). It then adds 30 days to each createdAt date to see if it's greater than NOW(). MySQL will sometimes automatically optimize this, and it depends on your version and some other factors, but in general, performing a function against the createdAt date for each row to compare it to a constant expression is bad because MySQL can't utilize an index on the createdAt column.</p> <p>So, you should convert:</p> <pre><code>AND DATE_ADD( j.createdAt, INTERVAL 30 DAY ) > NOW() </code></pre> <p>To this:</p> <pre><code>AND j.createdAt > DATE_ADD( NOW(), INTERVAL -30 DAY ) </code></pre> <p>This leaves j.createdAt column as a plain column, so MySQL can now utilize any index against the column to find dates that are less than 30 days in the past.</p> <p>It's the equivalent of standing in a room with 100 people and asking them to add 30 days to their birthday, then asking who's calculated date is greater than today. You just made 100 people do work. Instead, precalculate the criteria by subtracting 30 days from today's date and simply ask if anyone's birthday is greater than that date. You only had to do one calculation, and it saved those 100 people from having to do the hard work.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload