views:

74

answers:

4

Using the ADO.NET MySQL Connector, what is a good way to fetch lots of records (1000+) by primary key?

I have a table with just a few small columns, and a VARCHAR(128) primary key. Currently it has about 100k entries, but this will become more in the future.

In the beginning, I thought I would use the SQL IN statement:

SELECT * FROM `table` WHERE `id` IN ('key1', 'key2', [...], 'key1000')

But with this the query could be come very long, and also I would have to manually escape quote characters in the keys etc.

Now I use a MySQL MEMORY table (tempid INT, id VARCHAR(128)) to first upload all the keys with prepared INSERT statements. Then I make a join to select all the existing keys, after which I clean up the mess in the memory table.

Is there a better way to do this?

Note: Ok maybe its not the best idea to have a string as primary key, but the question would be the same if the VARCHAR column would be a normal index.

Temporary table: So far it seems the solution is to put the data into a temporary table, and then JOIN, which is basically what I currently do (see above).

A: 

If your primary keys follow some pattern, you can select where key like 'abc%'.

If you want to get out 1000 at a time, in some kind of sequence, you may want to have another int column in your data table with a clustered index. This would do the same job as your current memory table - allow you to select by int range.

What is the nature of the primary key? It is anything meaningful?

Kirk Broadhurst
Nope the keys are completely random strings for that matter (they are e-mail message ids)
Christian
A: 

If you're concerned about performance I definitely wouldn't recommend an 'IN' clause. It's much better try do an INNER JOIN if you can.

You can either first insert all the values into a temporary table and join to that or do a sub-select. Best is to actually profile the changes and figure out what works best for you.

Jaco Pretorius
A short EXPLAIN SELECT reveals that MySQL can use the primary key index when selecting via 'IN' clause. How do you use a LEFT JOIN when the data is not present in the database, but only in the select query itself?
Christian
As you can see this is exactly what I am currently doing. Putting them in to a MEMORY table and then JOINing.
Christian
A: 

Why can't you consider using a Table valued parameter to push the keys in the form of a DataTable and fetch the matching records back?

Or

Simply you write a private method that can concatenate all the key codes from a provided collection and return a single string and pass that string to the query.

I think it may solve your problem.

Siva Gopal
String concatenation would imply that I cannot use MySQLParameters to put the keys into the query. Which means I have to escape them myself, which could bring security risks and character encoding problems. Unfortunately I am also dealing with non-ASCII characters.
Christian
Oh, are you using MySQL? I am sorry, i thought you are using Microsoft SQL server.
Siva Gopal
A: 

I've dealt with a similar situation in a Payroll system where the user needed to generate reports based on a selection of employees (eg. employees X,Y,Z... or employees that work in certain offices). I've built a filter window with all the employees and all the attributes that could be considered as a filter criteria, and had that window save selected employee id's in a filter table from the database. I did this because:

  1. Generating SELECT queries with dynamically generated IN filter is just ugly and highly unpractical.
  2. I could join that table in all my queries that needed to use the filter window.

Might not be the best solution out there but served, and still serves me very well.

devnull
Thanks, it is useful to know that someone has come up with a similar solution and is satisfied :-)
Christian
Flagged this as the answer since it was the most useful reply
Christian