MySQL read parameter from file for select statement

Question

I have a select query as follows:

select * from abc where id IN ($list);

The problem with the length of variable $list, it may have a length of 4000-5000 characters, as a result of which the length of actually executed query increases and its get pretty slow.

Is there a method to store the values of $list in a file and ask MySQL to read it from that file, similar to LOAD DATA INFILE 'file_name' for insertion into table?

Yes but the issue is the resultant query is very large in length, reading that parameter form file would be great if possible — intivev
– intivev, Commented May 28, 2013 at 18:31
The query size should not matter so much, this is why I asked. I will post an answer in a minute. — Eugen Rieck
– Eugen Rieck, Commented May 28, 2013 at 18:32

Eugen Rieck · Accepted Answer · 2013-05-28 18:34:43Z

5

Yes, you can (TM)!

First step: Use CREATE TEMPORARY TABLE temp (id ... PRIMARY KEY) and LOAD DATA INFILE ... to create and fill a temporary table holding your value list
Second step: Run SELECT abc.id FROM abc INNER JOIN temp ON abc.id=temp.id

I have the strong impression this only checks out as a win, if you use the same value list quite a few times.

answered May 28, 2013 at 18:34

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL read parameter from file for select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related