CodeIgniter Forums
Bug with large IN query - Printable Version

+- CodeIgniter Forums (
+-- Forum: Using CodeIgniter (
+--- Forum: General Help (
+--- Thread: Bug with large IN query (/showthread.php?tid=71271)

Bug with large IN query - michaelv - 07-25-2018

i found a bug when dealing with large In query (approx 1000 ids)

preg_match(): Compilation failed: regular expression is too large at offset 43029 database/DB_query_builder.php 2415

my query is:

SELECT blabla ...
FROM `table xxx` `x`
WHERE `x`.`afficher` = 1
AND `id_prod` IN (12666,29593,20398,17415,..... over 1000 ids)
GROUP BY `x`.`id_prod`

RE: Bug with large IN query - php_rocs - 07-25-2018


Suggestion 1: Is your not in list shorter then you in list?
Suggestion 2: Create a subquery of the table with the wanted values and then join it to itself. I'm assuming that there is an easier way to identify the wanted id_prods.

RE: Bug with large IN query - michaelv - 07-25-2018

i managed to limit ids to 500 not more
but there is a problem somewhere in Core, so i give info de DEVS

RE: Bug with large IN query - dave friend - 07-25-2018

It's actually a problem of regex. If the string gets too big it runs run into a recursion limit.

Instead of using Query Builder you might be better off using a hand-crafted query string as an argument to $this->db->query('your hand-crafted query');

Depending on how you are building the "in" list you might be able to break it down into manageable bites. Here's the basic idea.

PHP Code:
$ids_chunk array_chunk($all_ids250); //or whatever size can be handled
foreach($ids_chunk as $chunk)

RE: Bug with large IN query - zurtri - 07-25-2018

I must admit having a list of 500 or 1000 IDs to check for in a IN() list, seems to be a lot.

I recognise that the world is a dirty place, and this might be the only solution - but it might be worth investigating if there is a better way.

I know this doesn't answer the exact question - but it is worth considering WHY you need such a list.

Having said all that - respect for what you're doing - sometimes we have to code nasty things for reasons not of our choosing.

RE: Bug with large IN query - michaelv - 07-26-2018

dear all thanks for your reply
i just follow the "bug" i found
this code was for previously viewd items on an ecommerce website
we never excpected a user to see more than 1000 produtcs in a session
but botnet do
i'll manage to recode this stuff or simply limit the previously view items to 100 or so

question, in term on performence, what would be better ?
100 lines like this:
$this->db->or_where_in('id_prod', $chunk);
or 100 Ids in a IN clause ?

RE: Bug with large IN query - php_rocs - 07-26-2018


You could easily test that yourself.  All you have to do is test both queries in MySQL Workbench.  

Also, to give your query more flexibility try the query binding approach ( ).

PHP Code:
$prod_list = array(12666,29593,20398,17415,..... over 1000 ids);
$qry "SELECT blabla ... FROM xxx x WHERE x.afficher = 1 AND id_prod IN ? GROUP BY x.id_prod ORDER BY RAND() LIMIT ? ";