March 12th, 2014, 05:33 AM
best way to design mysql database table so that columns can be searched quickly
Can someone please explain to me how MySQL searches through a database of multiple tables?
The reason for the question is that I am trying to determine the best way to design A database that would allow me to index many searchable columns within the tables.
It might be best if I show you what I mean;
I have a user entity that has many categories like;
- Main language spoken
- Country visited
- Available date
I expect to have millions of registered USERS s and will ALLOW members to search the database by one or many of the above categories..
Is it best therefore to keep all these columns in one table and then index each of them?. I however read somewhere that indexing several columns on a table affects performance .
Alternatively, should I make separate tables for EACH of these main items and then just have the user_ id indexed in each of these tables. (i.e MySQL would then make a separate FETCH to each of these table to fetch all the Id in each table).
I understand however that JOIN are a costly operation for MySQL to perform and that its quicker for it to work from one table.
I would be very grateful if someone could explain the right approach and also explain how the engine conducts the search. I seem to be getting contrary information from many different sources.
March 12th, 2014, 07:06 AM
your first step, before indexing, is to normalize the data
when that's done, come back and show us your normalized tables and then we'll talk about indexes
March 13th, 2014, 01:46 AM
Originally Posted by r937
thank you so much for your reply. i will start normalizing it now and get straight back to you.