0

I slept in school on database lesson and it sounded like nonsense anyway what they did in Microsoft Access. In MySQL everything is nice and clear. However i kind of puzzled by how to link tables together.

I currently use this style

primary table users primary key user_id other keys irrelevant

table documents primary key document_id second column user_id (from user table)

table schedule primary key document_id second column user_id (from user table)

table interview primary key document_id second column user_id (from user table)

and so on 20 more tables that connect between each other by user_id.

and in pages i do one query where i join all tables. sometimes 10 tables or 20 tables join like below without any speed problem takes on average about 1 second for query to run. Tip for speed - to have index in other tables based for the column you use in LEFT JOIN criteria or be numeric.

SELECT users.first_name,
       documents.document_date,
       interview.interview_result,
       ...
  FROM users
    LEFT JOIN documents ON documents.user_id = users.user_id    
    LEFT JOIN interview ON documents.user_id = users.user_id    
    ...
      LIMIT 0, 20

I know some people code like 5 different queries either sequential (one query selects data used in another query e.g. with loop then data from second query used in third query condition trough loop or smth) or independently select data from 5 tables and then in PHP join it all together in array based on user id.

Also inside tables i have several records per user e.g. in "documents" i have several records per user for each document we need from him they differ by "document_type" field.

However i had to do a page where i get all data for user from each table so query was not too practical and slow as hell to execute.

SELECT users.first_name,
       doc_resume.document_date AS resume_date,
       doc_passport.document_date AS passport_date,
       doc_photo.document_date AS photo_date,
       interview.interview_result,
       ...
  FROM users
    LEFT JOIN documents AS doc_resume ON doc_resume.user_id = users.user_id WHERE doc_resume.document_type = 1   
    LEFT JOIN documents AS doc_passport ON doc_passport.user_id = users.user_id WHERE doc_passport.document_type = 2   
    LEFT JOIN documents AS doc_photo ON doc_photo.user_id = users.user_id WHERE doc_photo.document_type = 3   
    LEFT JOIN interview ON documents.user_id = users.user_id    
    ...
      LIMIT 0, 20

Now i might need to design system where i have to get all data for user all the time, so obviously this approach of selecting with one query is not the best in this situation.

NOW THE QUESTION:

I rather have to select independently and then join in PHP or there's way to organize database so i can do it all in one query?

I was thinking maybe if i DON'T put user_id in each table and rather have one table called "link_table" where i have primary key "link_id" (not used), second column user_id, third column document_id (key from documents table) fourth column interview_id (key from interview table)

But then after writing this question i realized that in link table i will still have many records per each user e.g. for each record in documents i will have record link_table in link table, so it actually does not solve anything. or am i wrong.

Edit: Actually i wanted to know not about speed, but rather about should i use dedicated table for linking tables together or not is there any other advantages/disadvantages than speed?

Petja Zaichikov
  • 275
  • 3
  • 12
  • How did you come to the conclusion that the query was the slow part of your request? – Justin Wood Dec 13 '13 at 21:29
  • 4
    "I rather have to select independently and then join in PHP" No, horrible idea. – Mike Purcell Dec 13 '13 at 21:30
  • What having a link table solves is it allows you to link documents to things other than users. Or maybe one document linked to 50 users without requiring duplicated data. – Kevin B Dec 13 '13 at 21:33
  • You need to start off at the basics - namely your model. Depending on the relations, the model will determine if the user_id should be in those tables or if you need a M:N table. Also, your syntax is wrong - there is no `WHERE` as part of a `LEFT JOIN` clause – AgRizzo Dec 13 '13 at 21:34
  • Try using UNION ALL.. i think it would be faster.. http://stackoverflow.com/questions/3374459/can-union-all-be-faster-than-joins-or-do-my-joins-just-suck – Hardy Dec 13 '13 at 21:35
  • As @AgRizzo said, your syntax is wrong. Your `WHERE` clause in the joins should be an `AND`. So: `LEFT JOIN documents AS doc_resume ON doc_resume.user_id = users.user_id AND doc_resume.document_type = 1` – TheCarver Dec 13 '13 at 21:39
  • 1
    At the top of your question you say you have approximately 20 tables and then go on to say you join 10-20 tables without any speed problems - around 1-second. Then later on you say that you then had to get data from all those tables and was slow as hell. This tells me that there is something wrong with selecting the additional fields, not really joining all the tables. Do any of the fields you're returning in the select clause have large amounts of data? Do each of the fields in the select clause have indexes, not just the userID and the fields in the joins? – TheCarver Dec 13 '13 at 21:47
  • Have you tried adding `EXPLAIN` before the `SELECT` and printing its response? So you'd have `EXPLAIN SELECT users.first_name,...`. This will tell you how many rows were scanned. Also, can you tell us how many rows are in your biggest table? – TheCarver Dec 13 '13 at 21:51
  • Also, do you have a `WHERE` clause? If you do, can you post that in your question? Especially if you're using `LIKE`. – TheCarver Dec 13 '13 at 22:26
  • **Justin Wood** i know its query because i run it in phpMyAdmin, anyway speed resolved after adding index for each field of documents that i was joining by. – Petja Zaichikov Dec 14 '13 at 00:29
  • **PaparazzoKid** why would i need where if i selecting all info for all users in database. – Petja Zaichikov Dec 14 '13 at 00:30
  • 1
    You have used WHERE in your own query... We don't know why you did either, but you wrote the code. If you did decide you need a WHERE clause you could of course use AND instead. – user1641165 Dec 14 '13 at 01:35
  • @PetjaZaichikov, If you've resolved the question then delete it or write your own answer. – TheCarver Dec 14 '13 at 17:20

0 Answers0