ansaurus

Question

Query select a bulk of IDs from a table - SQL

Answer 1

A:

Do those id's actually reference the table with 1M rows?

If so, you could use SELECT * ids FROM <1M table>

where ids is the ID column and where "1M table" is the name of the table which holds the 1M rows.

but I don't think I really understand your question...

Aelux 2010-07-31 13:52:42

Yes, as I stated, the list of ~100K IDs belong to that table. Not sure if I understood your suggestion - you suggested to run ~100K select queries?

Doori Bar 2010-07-31 13:59:01

No no... You run ONE select query for all the IDs in the 1M table, but I still don't understand what you're wanting... You have 1 million rows but only 100k ids? Shouldn't every row have an ID if each has and ID column?

Aelux 2010-07-31 14:11:30

Let me clarify. The ~1M table has ~1M IDs. The application has a list of only ~100K IDs to select. How would you suggest me to query ~100K bulk IDs?

Doori Bar 2010-07-31 14:21:24

I see now... so you want to search for the 100k ids WITHIN the 1M table. There is one easy way to do it, if you wrote a program that loaded all the rows into memory and then looped through them consecutively to find all the corresponding ids. Short of that, you'd have to write a SELET query for each ID.

Aelux 2010-07-31 14:31:25

To be honest, creating a program that does that would probably only take me like 10 minutes in C# lol.

Aelux 2010-07-31 14:40:04

Answer 2

A:

You could do it in one query, something like

SELECT * FROM large_table WHERE id IN (...)

Insert a comma-separated list of IDs where I put the ... Unfortunately, there is no easy way that I know of to parametrize this, so you need to be extra-super careful to avoid SQL injection vulnerabilities.

tdammers 2010-07-31 14:23:29

Select 100K IDs within 1 query? do you happen to know the maximum length of such query?

Doori Bar 2010-07-31 14:27:39

Answer 3

A:

A temporary table which holds the 100k IDs seems like a good solution. Don't insert them one by one though ; INSERT ... VALUES syntax in MySQL accepts the insertion of multiple rows.

By the way, where do you get your 100k IDs, if it's not from the database ? If they come from a preceding request, I'd suggest to have it fill the temporary table.

Edit : For a more portable way of multiple insert :

INSERT INTO mytable (col1, col2) SELECT 'foo', 0 UNION SELECT 'bar', 1

Vanmachin 2010-07-31 14:29:51

What would be the maximum length of such insert queries? Is it common to be able to insert ~100K at once?

Doori Bar 2010-07-31 14:38:17

@Vanmachin, SQLite can't do multiple inserts like that, though.

MPelletier 2010-07-31 16:04:05

I don't know how MySQL scale with this type of request, but to avoid a too large request, I'd go for a hundred IDs by INSERT as a start.@MPelletier : answer edited

Vanmachin 2010-07-31 16:45:35

@Vanmachin: I was wondering, why wouldn't you suggest to simply select 100 IDs at a time, which means instead of ~100K select queries - It's ~1K select queries, without the need for the initial inserts/temporary table?

Doori Bar 2010-07-31 16:51:21

@MPelletier : didn't test it, just read SQLite manual which didn't said it was not good

Vanmachin 2010-08-01 18:46:06

@Doori Bar : this works too, but in case you need to additional treatment on the server, you have your IDs at hand ; plus, if the unique SELECT following have multiple joins, the query engine will be more efficient

Vanmachin 2010-08-01 18:46:23

You need to use parameterized queries when you use sqlite else sqlite will spend too much time parsing all those different queries. See http://stackoverflow.com/questions/904796/how-do-i-get-around-the-problem-in-sqlite-and-c/926251#926251 . Just do a lot of insert..values statements and bundle all those insert..value statements in one transaction because the commit is slow in sqlite.

TTT 2010-08-04 04:10:25

Sqlite's insert is fast but the commit is slow.

TTT 2010-08-04 04:11:23

ansaurus

tags:

views:

answers:

Query select a bulk of IDs from a table - SQL

related questions