Hi all,
I have a search method which needs to get quicker, due to the growing size of users and the db.

The db is in second normal state, however for the initial search no keys are used to match a result, the db will return many results which r then validated.

Once the valid options are returned the indexing comes in to its own and I have no complaints.

But I would be greatful of any suggestions, references or technical docs on how to speed up my initial search.

Cheers LazyTrucker.

With respect to your query, please post the output of:

1) EXPLAIN SELECT .... \G for the query you want to speed up
2) SHOW CREATE TABLE foo; for all tables involved in the query
3) SHOW INDEX FROM foo \G for all tables involved in the query

--
felix

If I was to provide all of the above information it would turn out to be a vry lengthy doc, and I would be breaching my contract as a result.
However I shall break it down if u feel you could still help, so here goes:

Basically this is a train journey and timetable database which has three basic tables in order to find a routing.

table 1: Bolt on (this may be an extra leg in order to make the departure)
table 2: Main (this is where the main journey details are held)
table 3: Bolt on (this may be an extra leg in order to make the arrival)

So the journey may be found 4 different ways.

1:Using only the main (table 2).
2:Using a bolt on (table 1) and the main (table 2).
3:Using the main (table 2) and a bolt on (table 3)
4:Using a bolt on (table 1) the main (table 2) and a bolt on (table 3)

In order to find a journey I search from the arrival back the way, and use 2 search's to do so.

1tarting from the main(table 2) finding the arrival then determining the point of origin.

2tarting from the bolt on(table 3) finding the arrival then determining the point of origin.

So essentially I am only entering one search parameter, the arrival then validating the departure and looping through the records.

I have used joins for this purpose and found the performance dramtically reduced with the excution taking up to 120secs to complete.

I would welcome an alternative method, or ne guidance on how to speed up the processing.
I am familiar with using arrays or the dictionary object to hold the record information but Im not sure if this would suit my purpose, or make much difference as the information is discarded after the life of the record and not re-used.

Cheers again hope this clears things up slightly, LazyTrucker

Why do you need three tables? Is it not possible to put all data into one timetable?
Not sure what you mean with "bolt on".

Are your tables properly indexed? One way to find out is to look at the output I asked you to post in my previous reply.

Consider hiring a consultant and have him sign an NDA if you are worried about confidentiality.

--
felix