Take the 2-minute tour ×

I have some location data, which is in a table locations with the key being the unique location_id

I have some user data, which is in a table users with the key being the unique user_id

Two ways I was thinking of linking these two together:

  1. I can put the 'location' in each user's data.

    'SELECT user_id FROM users WHERE location = "LOCATIONID";'
    //this IS NOT searching with the table's key
    //this does not require an explode
    //this stores 1 integer per user
  2. I can also put the 'userIDs' as a comma delimited string of ids into each location's data.

    'SELECT userIDs FROM locations WHERE location_id = "LOCATIONID";'
    //this IS searching with the tables key
    //this needs an explode() once the comma delimited list is retrieved
    //this stores 1 string of user ids per location

so I wonder, which would be most efficient. I'm not really sure how much the size of the data stored could also impact the speed. I want retrievals that are as fast as possible when trying to find out which users are at which location.

This is just an example, and there will be many other tables like location to compare to the users, so the efficiency, or lack of, will be multiplied across the whole system.

share|improve this question
can many user be from same location ? And can user be associated with many locations ? –  tereško May 2 '11 at 1:19
there can be many users in a location, but each user could only be at one location. –  meticulou May 2 '11 at 1:24
add comment

4 Answers

up vote 3 down vote accepted

Stick with option 1. Keep your database tables normalised as much as possible till you know you have a performance problem.

There's a whole slew of problems with option 2, including the lack of ability to then use the user ID's till you pull them into PHP and then having to fire off lots more SQL queries for each ID. This is extremely inefficient. Do as much inside MySQL as possible, the optimisations that the database layer can do while running the query will easily be a lot quicker than anything you write in PHP.

Regarding your point about not searching on the primary key, you should add an index to the location column. All columns that are in a WHERE clause should be indexed as a general rule. This negates the issue of not searching on the primary key, as the primary key is just another type of index for the purposes of performance.

share|improve this answer
do the indexes have to be unique? or just the primary key? because there would be multiple users with the same location. –  meticulou May 2 '11 at 1:31
primary keys are indexed –  tereško May 2 '11 at 1:33
@meticulou: Regular indexes can either be unique or not. In this case you probably don't want it to be. I've added a link to documentation in my answer. Primary keys are always unique indexes. –  Matthew Scharley May 2 '11 at 1:38
ah ok! i just thought all indexes had to be unique for some reason. this answers my questions perfectly. –  meticulou May 2 '11 at 1:57
add comment

Use the first one to keep your data normalized. You can then query for all users for a location directly from the database without having to go back to the database for each user.

Be sure to add the correct index on your users table too.

CREATE TABLE locations (

    location INT,
    INDEX ix_location (location)

Or to only add the index

ALTER TABLE users ADD INDEX ix_location(location);
share|improve this answer
add comment

Have you heard of foreign key ?

get details from many tables tables using join .

You can use of sub query also.

As you said there are two tables users and locations.

Keep userid as foreign key in locations and fetch it based on that.

share|improve this answer
foreign key seems like it would speed things up even more. I would make location a foreign key in users if I wanted multiple users per location, right? –  meticulou May 2 '11 at 1:38
@meticulou: A foreign key is just a different sort of index in MySQL, more of an informational thing. They are only explicitly supported if you are using InnoDB. It's more of a concept than an actual part of MySQL. –  Matthew Scharley May 2 '11 at 1:48
@meticulou you cant make location a foreign key. make location id foreign key . see an eg brisbane is in US and it is in AUS so give an id and make it foreign key will be a good idea. In your case this may not happen . but thats the good practise –  zod May 2 '11 at 1:52
add comment

When you store the user IDs as a comma-separated list in a table, that table is not normalized (especially it violates the first normal form, item 4).

It is perfectly valid to denormalize tables for optimization purposes. But only after you have measured that this is where the bottleneck actually is in your specific situation. This, however, can only be determined if you know which query is executed how often, how long they take and whether the performance of the query is critical (in relation to other queries).

Stick with option 1 unless you know exactly why you have to denormalize your table.

share|improve this answer
good link, thanks. –  meticulou May 2 '11 at 3:05
add comment

Your Answer


