Optimizing MySQL SELECT with many cols

Anon

Hi,
I have a query that I use 100,000s of times on a table with >70,000 rows and growing fast that I really need to speed up. If anyone could offer any advice it would be so helpful.

essentially the query is of the form:

SELECT * FROM tbl_name WHERE col1=val1 OR col2=val2 ... OR col30=val30;

I thought I would create an INDEX on the 30 columns using:

INDEX index_name ON tbl_name
(col1, col2, ... col30);

But There is a 16 col limit on indexes. Does anyone know how I can overcome this? Create two indexes maybe?? Thanks for any advice.
-Frank

reagent

Are you implying that you really do have a table with the column names col1, col2, etc..? I hope not, but I'm sure you already knew that.

It sounds as if this is not the best way to organize your data, you may be better off splitting your data among different tables and joining it together in your select.

If you give me more information about the structure of your table, and what you are trying to do, maybe I can help?

Anon

you said too much p... i usually just do something like this.......

POST THE DAMN CODE IF YOU WANT ME TO FIX IT!

Anon

Sure,
Here is an example of code although there really is nothing to fix... I am just trying to optimize it... I have to run this query (in this case the code is in perl):

$query = $db->prepare("SELECT * from urls WHERE (k1='$wid' OR k2='$wid' OR
k3='$wid' OR k4='$wid' OR k5='$wid' OR k6='$wid' OR k7='$wid' OR k8='$wid' OR
k9='$wid' OR k10='$wid' OR k11='$wid' OR k12='$wid' OR k13='$wid' OR
k14='$wid' OR k15='$wid' OR k16='$wid' OR k17='$wid' OR k18='$wid' OR
k19='$wid' OR k20='$wid' OR k21='$wid' OR k22='$wid' OR k23='$wid' OR
k24='$wid' OR k25='$wid' OR k26='$wid' OR k27='$wid' OR k28='$wid' OR
k29='$wid' OR k30='$wid')");
$query->execute;

Basically it is a search engine and I want to get all urls with any keyword id (k1..k30) that matches the given word id.
k1..k30 and $wid are integers which correspond to the primary key in a wordlist.

If I only had to do this once per search it would be fine. But in this case I am mantaining a cache of all urls for a given keyword id that I constantly have to update as I spider sites.

There are tradeoffs no matter how I assign my tables. This happens to be my bottleneck and I purposely designed it this way so the bottleneck would be in creating the cache of word rankings. I am wondering if there is any way I can optimize it as it gets too slow with 70K urls. I thought an INDEX would work but with 30 cols this is not straight forward. I have to update approx. 10K words a day and with 10 concurent processes (the max I am allowed) this takes roughly 4 hours. I would like to increase words proccessed and urls in the table dramatically. I know with less urls this goes much faster. My only recourse may be more computing power but for now I would like to try to optimize this if possible.

Thanks again for everyones helpful comments.
-Frank

reagent

At a glance, i would break this up into 3 tables (maybe more, I'm hurrying out the door)

a site table that stores:
site_id_no
url_tx

a keywords table that stores:
keyword_id_no
keyword_tx

and a site_keywords table that stores:
site_id_no
keyword_id_no

your design is most definitely flawed, i would redesign it before you get too much data in there to handle

good luck
p.

Anon

Thanks for your advice. I think I will try to implement it your way. I remember I didn't do it that way in the beginning because I was afraid of the "site_keywords" table becoming to large... but I do agree that that would be the proper way to do it. Thanks!

-Frank

inkel

Frank:

I recommend you to read the article "Database Normalization and Design Techniques" (http://www.phpbuilder.com/columns/barry20000731.php3), it will be very useful to you.

HTH

inkel