SQL_CALC_FOUND_ROWS not working sometimes

Question

$query->select('SQL_CALC_FOUND_ROWS i.title,i.fulltext,i.shorttext');
        $query->from('#__items as i');
$query->where('i.shorttext LIKE "%'.$word.'%");

with this condition everything is right and count of rows is real...for example if it return 21 items...it is real...(the real returned result is 21)

but in this code:

 $query->select('SQL_CALC_FOUND_ROWS i.title,i.fulltext,i.shorttext');
            $query->from('#__items as i');
$query->where('i.shorttext LIKE "%'.$word.'%" 
            OR i.fulltext LIKE "%'.$word.'%" 
            OR i.title LIKE "%'.$word.'%"');

The code return count of rows to me 63! (3 times bigger the real one.)

of course both of them print real times (not dublicate).

what's the wrong? tnx

I changed to this (added brackets around the three or conditions) and the problem solved.

 $query->select('SQL_CALC_FOUND_ROWS i.title,i.fulltext,i.shorttext');
            $query->from('#__items as i');
$query->where('(i.shorttext LIKE "%'.$word.'%" 
            OR i.fulltext LIKE "%'.$word.'%" 
            OR i.title LIKE "%'.$word.'%")');

Does your statement have a `LIMIT` clause (you are not showing one)? If it does not, then there is no purpose to `SQL_CALC_FOUND_ROWS`. — eggyal, Feb 23 '14 at 10:43
What does the explain plan look like? Is it the same for the original and the fixed version or different? Also what is the `$query->where` syntax? Are you using something that might rerwrite the query as a `UNION`? — Martin Smith, Feb 23 '14 at 11:52
Are you able to retrieve the actual SQL executed from both versions? Can you reproduce this by running the SQL in (say) MySQL query workbench? — Martin Smith, Feb 23 '14 at 12:10

score 0 · Answer 1 · answered Feb 23 '14 at 10:41

0

The code returns 63, because your query returns 63 rows (or you changed the variable afterwards in PHP).

It's either a correct result or you extracted the number wrong. There's no magic from MySQL at this point.

answered Feb 23 '14 at 10:41

Ulrich Thomas Gabor

6,584
4
27
41

but whole of records is 21! – user3307827 Feb 23 '14 at 10:56
Either the number is correct or you retrieve the number falsely. – Ulrich Thomas Gabor Feb 23 '14 at 10:58

SQL_CALC_FOUND_ROWS not working sometimes

1 Answers1