how can i deal with big mysql table with php for login authentication

0
votes

I am working on a project using codeigniter framework.There are different types of users.Now we have 4 user types.I am using different registration table for every user like

registration_table_user_type1,

registration_table_user_type2,

registration_table_user_type3,

registration_table_user_type4.

But now i am using one login form with username and password input field.And i want to create a table for login authentication.This table will be like that

login_table(username,password,user_type,registration_id)

but i am worried about website.Because there will be more than 1000000 users for different type of user.How can i write mysql query for doing faster my website.I want some advice ,Please help me.

2
phpmysqldatabasecodeigniteroptimization
Hi do have you any idea about CRON - Nes
Google "database indexes"; though a different table for each user type is bad design - Mark Baker
You could use only one table for users and an column with user type - Ajith S
Have a look into UNION queries. Because your data is split over 4 tables there is probably very little you can do (if you have your indices set correctly) that will speed up the data retrieval. - user2417483
@Nes: How exactly does CRON help here? - symcbean

2 Answers

1
votes

I would suggest to try and normalise your database design first. It seems strange to have 4 different tables which might have similar data (except for the type). Typically one would have a table (USER) and in the table USER have a column representing either type itself or a foreign key to a TYPE table.

By using a single USER table, you then have the ability to leverage the database optimisation (indexing for one) and minimise the overhead.

0
votes

Your database schema is not normalized, use this schema instead with innoDB engine

CREATE TABLE `user_group` (
`id` tinyint NOT NULL AUTO_INCREMENT,
`label` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `user_account` (
`id` int NOT NULL AUTO_INCREMENT,
`gid` tinyint NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`gid`) REFERENCES user_group(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);