Search overload

[Thierry]

I have a table with about 5000 records in it and roughly 50 fields, all with a lot of data in them.

Naturally you can search in that table, but sometimes the results seem to be more then SQL can handle, the limit seems to be around 2000 records.

Its not a question of getting the information, but having 2000 times an "OR id = '$id'" in the query.

Right now I can't think of an alternative to get all the possible returned records, since it could be anywhere between zero and 5000.

 

Any ways to get all the record id's without overloading the query with OR's?

[veridicus]

ORs are expensive.  First simply make sure you have indexes on the fields you're searching, and unique indexes where possible.  That'll help the database find records faster.  For such a small set of data this might suffice.  Also don't use LIKE conditionals unless you have to, and if you do try to not use a wildcard at the start of the field (i.e. LIKE 'smith%' instead of '%smith%').

 

If it's still too slow after all that, use the EXPLAIN statement and post the output of it here so we can investigate the details.

[Thierry]

Its not the searching in the database that's the problem, its showing all the ids.

For instance, I could (in theory) have record 1,3,5,7,9 and then all the way to 5000.

The only way in which it could show all the ids then would be by using "OR id = '1' OR id = '3' etc".

 

I suppose I could do it differently, running a thousand queries with one id (using a loop) then one query with a thousand ids but I think that would be very slow loading.

[igor berger]

You can select all records by using select *

You can select one by using where id = what ever

 

You can select a set where id < bla and id > blabla

 

Look up the syntax....

[Thierry]

Thats not the problem.

I cant use WHERE id > '0' AND id < '6' because that wouldn't work if I want to show id 1,3,5 but not id 2 and 4.

Also, its not at all related to the fields with * (which I dont use).

[igor berger]

Then you have to give logic to your data!

 

categories records

 

blue 1 3 5

red 7 9 3

etc...

 

It is called stractual data base

 

So you may need to categorize data by similaraties girl with one leg, a man with 3 arms, etc...

[Thierry]

Not sure how I could succesfully do that at this point.

I have 5000 persons in my database, each with completely unique information.

They do have a few status data that others have as well, but I still dont see how I could search by those instead of IDs.

[igor berger]

Data base design is a job tytle!

 

It is not just putting 5,000 records and using select.

 

You can search all by a key word, subset key word, etc....

 

But if you want to have 1000 key words to search, why not break it down to 100 data bases of same date!