1

enter image description here

I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, and courses etc by searching in all tables (it could be area, city, institute name, courses or anything what we have).

But the problem is I have to make joins between all the tables and it is getting too slow. Can we optimize these tables to make the database more search/filter friendly?

For now I am using MongoDB to cache these data in single collection. As MongoDB isn't a relation database system, we have to compromise with many features.

Is MongoDB the only solution for me? Or can these be optimized?

My Query is

SELECT i.name, GROUP_CONCAT(h.hotspot_name) hotspots,
       GROUP_CONCAT(ac.accreditation) accreditations, c.city_name from institutes i
left join areas a on i.area_id = a.area_id
left JOIN districts d on a.district_id = d.district_id
LEFT JOIN cities c on c.city_id = d.city_id
LEFT JOIN institute_hotspots ih on ih.inst_id = i.inst_id
LEFT JOIN hotspots h on ih.hotspot_id = h.hotspot_id
LEFT JOIN institute_accr ia on i.inst_id = ia.inst_id
LEFT JOIN accreditations ac on ia.accr_id = ac.accreditation_id
LEFT JOIN institute_courses ic ON i.inst_id = ic.inst_id
LEFT JOIN courses co on ic.course_id = co.id
LEFT JOIN course_names cn on co.course_id = cn.id
LEFT JOIN subcourses sc on co.subcourse_id = sc.id
LEFT JOIN course_types ct on co.type_id = ct.id
LEFT JOIN course_levels cl on co.level_id = cl.id
LEFT JOIN course_streams cs on co.stream_id = cs.id
LEFT JOIN course_category_relation ccr ON co.id = ccr.course_id
LEFT JOIN course_categories cc ON ccr.category_id = cc.id
WHERE i.name LIKE '%{QUERY}%'
OR i.name LIKE '%{QUERY}%'
OR  c.city_name LIKE '%{QUERY}%'
OR  a.area_name LIKE '%{QUERY}%'
OR  d.district_name LIKE '%{QUERY}%'
OR  h.hotspot_name LIKE '%{QUERY}%'
OR  ac.accreditation LIKE '%{QUERY}%'
OR  cn.name LIKE '%{QUERY}%'
OR  sc.name LIKE '%{QUERY}%'
OR  cl.name LIKE '%{QUERY}%'
OR  ct.name LIKE '%{QUERY}%'
OR  cs.name LIKE '%{QUERY}%'
OR  cc.name LIKE '%{QUERY}%'

GROUP BY i.inst_id
limit 10

It does not even run on my local system.

While preparing the diagram, I have changed some field names.

More detailed view

  • Have you tried adding indices to support your queries? While joining this many tables in a single query is not unheard of, but if you are experiencing performance issues, perhaps it would be better to split your single query into several of smaller scale? Updating your question with an example of a query you have tried might be helpful. – Andriy M Jan 11 '16 at 17:58
  • I have update with query. Please take look. – Md Adil Jan 12 '16 at 7:34
1

34 tables is a lot.

  • Over-nomalization
  • Many-to-many mappings that should be 1:many
  • Excessive use of "added by"
  • Use minimal datatypes

Some examples:

Because of the nested nature of districts-cities-states, and the fact that city and state names "never" change, there is no need to carry that normalization past district (or maybe even area).

I suggest it is OK to use the 2-letter state abbreviations and not have the full name. Note: that should be CHAR(2) CHARACTER SET ascii to minimize the size (2 bytes).

Don't bother 'normalizing' simple things like keywords, course types, etc. When there is a relatively small and static number of such things, consider using ENUM for them.

An "accreditation" is for one institute, correct? It is not a blanket for many institutes? So that is really 1:many, not many:many. Hence, the intermediate table is unnecessary. (Some relations should continue to be many:many.)

For dozens of items, use TINYINT UNSIGNED (1 bytes), not INT UNSIGNED (4 bytes). For hundreds, maybe thousands, use SMALLINT UNSIGNED (2 bytes).

What is a type_id?

| improve this answer | |
  • Thanks Rick, i appreciat it. But i wanted table structure something like wordpress uses. Where we have minimum tables. There were some more table it was getting too complecated in digram, so i deleted it. And table for institutes type also deleted which was belonging to type id. – Md Adil Jan 12 '16 at 6:30
  • I have update with query. Please take a look. – Md Adil Jan 12 '16 at 7:34
  • Remove LEFT unless the JOINs are really optional. – Rick James Jan 12 '16 at 19:40
  • That SELECT will have to put all the data together (using the joins), then test lots of columns, and finally group by and deliver 10 results. Very tedious and slow. LIKE with a leading wild card cannot use an index. – Rick James Jan 12 '16 at 19:42
  • For that query, you might be better to create another table with all the names collected together in a single text column. The other column would be inst_id. Search that table, then join to the morass of normalization to get the desired 10 rows. – Rick James Jan 12 '16 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.