0

I am writing a query for a database with 2 large tables. It takes forever to get the results. I have limited the results to a specific year although ideally I want to get them all at once. The sql is: 

$sql ="SELECT month(table1.started) as month, count( table1.id ) AS count 
FROM table1 
INNER JOIN table2 ON table1.`sessionId`=table2.`sessionId` 
WHERE table2.item_id=".$id." AND YEAR(table1.started) = '".$year."' 
GROUP BY MONTH(started) 
ORDER BY MONTH(table1.started) ASC";

php: 

$result = $mysqli->query($sql, MYSQLI_USE_RESULT); //unbuffered does not speed it up
if ($result) {
    while($info = $result->fetch_assoc()) {$output.="....";}

There are about 145k rows in table1 (of which about 25k from the year in the query) and 250k in table2. While fetching these results the website is inaccessible. It takes about 10 seconds to get a resultset of only 340 items, to get one of a couple of thousand results takes more than 2 minutes. Querying results from table1 only is very fast, even for the complete table grouped by year and month. Is there a way to speed things up?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
bolvo
  • 361
  • 3
  • 13
  • **WARNING**: This has some severe [SQL injection bugs](http://bobby-tables.com/) because user data is used inside the query. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. **NEVER** put `$_POST`, `$_GET` or any user data directly in your query. – tadman Oct 22 '17 at 20:59
  • hello tadman, the $id and $year are both cast to an integer, there is no risk of sql injection this way – bolvo Oct 23 '17 at 06:52
  • Thanks your common sense, Clearly I did try to find a solution here and on a particularly popular search engine, but I did not find that result. Creating an index on table1.sessionId solved the issue entirely. – bolvo Oct 23 '17 at 07:20
  • Casting is *not* a general purpose escaping solution and if you forget, even once, you're wide open. Use prepared statements with placeholder values. That way you need to make several mistakes in a row before you get burned. – tadman Oct 23 '17 at 16:30

0 Answers0