Alternative or help for multiple SQL queries running too slow

Question

On one single page, I do have about 100 queries like

$sQuery = "SELECT * 
FROM  `users` 
HAVING  `team` = $team
AND  `group` = $group AND male = 1 AND `team1` > 0 AND `team2` > 0 AND status LIKE 'movies'";
$rResult = $pdo->query($sQuery)->fetchAll(); 
$results = count($rResult);

running as a cronjob to save the results of those queries in another table.

The users table has about 2.100.000 rows. Yes, 2.1m and growing but slowly growing. The results of each query have between 0 and 40 rows each, not more. Now my question: I guess there is a better way to get the results and save them to another table, so do you have any suggestions? Surprisingly, the cronjob takes too long to load and of course, freezes very often.

I have used prepared statements before, but those were even slower. At least I had the feeling. I hope you can help me here. Thank you very much!

Why on earth are you using HAVING in this query instead of WHERE? — Mark Baker, Aug 09 '17 at 17:15
Without understanding the data types, indexing, etc., we can't answer that. Even the table type can make a difference. — Difster, Aug 09 '17 at 17:16
So what Indexes do you have on this table; and what does an EXPLAIN show for the query? — Mark Baker, Aug 09 '17 at 17:16
Performance questions should include `EXPLAIN ANALYZE` and some information about table size, index, current time performance, desire time, etc. `Slow` is a relative term and we need a real value to compare. [**MySQL**](http://dba.stackexchange.com/questions/15371/how-do-i-get-the-execution-plan-for-a-view) [**How do I obtain a Query Execution Plan?**](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) — Juan Carlos Oropeza, Aug 09 '17 at 17:18
Ah ok, then I guess I have to check that first and be back in the next days. Thanks so far. — Robin Alexander, Aug 09 '17 at 17:19
As the found results are filtered results from about 40 rows, do you think it might be a lot faster if the results are stored in an array once and then run the calculations with those array values? — Robin Alexander, Aug 09 '17 at 17:24

score 1 · Answer 1 · answered Aug 09 '17 at 19:46

Don't use HAVING as your default filter option because they are not optimized as noticed on Which SQL statement is faster? (HAVING vs. WHERE...) in the MySQL manual on 13.2.9 SELECT Syntax:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

Use the WHERE keyword instead like everyone else. And use prepared statements as always for querys with variables:

$sql = "SELECT
            your, columns, here 
        FROM
            users
        WHERE
            team = ? AND
            group = ? AND
            male = 1 AND
            team1 > 0 AND
            team2 > 0 AND
            status = 'movies'";
$stmt = $db->prepare($sql);
// ...

Alternative or help for multiple SQL queries running too slow

1 Answers1