Commander,
The most efficient way is to normalize the database schema into proper entities, like so:
CREATE TABLE GeneList (
ListID INT UNSIGNED NOT NULL AUTO_INCREMENT
, PRIMARY KEY (ListID)
);
CREATE TABLE Gene (
GeneID INT UNSIGNED NOT NULL
, PRIMARY KEY (GeneID)
);
CREATE TABLE GeneList2Gene (
ListID INT UNSIGNED NOT NULL
, GeneID INT UNSIGNED NOT NULL
, PRIMARY KEY (ListID, GeneID)
);
You can then query for all genes in a specific list with ID # 459823 by executing:
SELECT GeneID
FROM GeneList2Gene
WHERE ListID = 459823;
or find all lists containing a gene # 727793 by executing:
SELECT ListID
FROM GeneList2Gene
WHERE GeneID = 727793;
Of course, I'm totalling guessing as to the data types... you'd know best what to use for genes identifiers...
Hope this helps,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com