1

I have a table which holds ~1M rows. My application has a list of ~100K IDs which belong to that table (the list being generated by the application layer).

Is there a common-method of how to query all of these IDs? ~100K Select queries? A temporary table which I insert the ~100K IDs to, and Select query via join the required table?

Thanks, Doori Bar

Doori Bar
  • 873
  • 2
  • 13
  • 20

3 Answers3

1

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
  • 20,353
  • 1
  • 39
  • 56
0

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...

Aedaeum
  • 305
  • 1
  • 11
  • 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 Jul 31 '10 at 13:59
  • 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? – Aedaeum Jul 31 '10 at 14:11
  • 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 Jul 31 '10 at 14:21
  • 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. – Aedaeum Jul 31 '10 at 14:31
  • To be honest, creating a program that does that would probably only take me like 10 minutes in C# lol. – Aedaeum Jul 31 '10 at 14:40
0

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
  • 46
  • 4
  • What would be the maximum length of such insert queries? Is it common to be able to insert ~100K at once? – Doori Bar Jul 31 '10 at 14:38
  • @Vanmachin, SQLite can't do multiple inserts like that, though. – MPelletier Jul 31 '10 at 16:04
  • 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 Jul 31 '10 at 16:45
  • @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 Jul 31 '10 at 16:51
  • @MPelletier : didn't test it, just read SQLite manual which didn't said it was not good – Vanmachin Aug 01 '10 at 18:46
  • @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 Aug 01 '10 at 18:46
  • 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 Aug 04 '10 at 04:10
  • Sqlite's insert is fast but the commit is slow. – TTT Aug 04 '10 at 04:11