Database performance from php calls

[phfilly]

Hi everyone,

I'm wondering about a potential problem I might get. My problem is lets say I have 2 tables one called speakers and clients. In the client table the client has the speaker ID's of 1,5,8(SAVED in a STRING field) - so I'm using explode() to get the values.

So now I have to call the speaker table 3 times to get the values of each speaker. This introduces the problem that it will get very expensive if there is alot of users online wouldn't it?

Is there an alternative to calling a table from an array of items or something?

I'm not too clued up about all the php approaches to this so any help will be appreciated!

I'm using mysql for the db.

[Traevel]

Are you still in a position to change the table structure? What you are describing sounds like a one-to-many (or many-to-many) relationship between speakers and clients. You could avoid saving ID's in a string by using a different structure.

client       speaker             speaker_client
--------     --------       ------------------------
id name      id name        client_id    speaker_id
1  jack      1  jason       1            1
2  john      2  jay         1            2

You could then request the values of speaker with something like:

SELECT * FROM speaker WHERE speaker_client.speaker_id = speaker.id AND speaker_client.client_id=1;

Even easier if you create a TABLE VIEW from a query like this, then you could retrieve them using something as simple as:

SELECT * FROM speaker_view WHERE client_id=1;

The advantage of this table structure is that you could include more information, for instance "topic of discussion" that is attached to the speaker/client relation.

Is there an alternative to calling a table from an array of items or something

You could use the IN clause.

SELECT * FROM speaker WHERE id IN (1, 5, 8);

With the above table structure you could also choose for an IN clause instead of the AND to retrieve speaker values, something like:

SELECT * FROM speaker WHERE id IN (SELECT speaker_id FROM speaker_client WHERE client_id=1);

This way you would not need to retrieve and split the 1, 5, 8 first.

[phfilly]

Thanks raevel I'm going to take a look at that IN clause approach. I Will mark it completed if it works.