CODEWITHSUNDEEP
mysqlsqldatabaseselect
Afsal Meerankutty
Afsal Meerankutty

Reputation: 366

How to Avoid Complete Table Scan in Database?

I want to creating a website where users can sign up in website and upload 20 images to their account. (planning to allow users to upload unlimited images in future)

I have two tables in database. One for keeping user data Table name - members Fields- userid, username, password, email. Other for saving image path Table name- images Fields - userid, imagepath

So I can display images of a user in his page searching image path from table 'images'

Things are working fine. But if the number of users grow this will become slower. For example - If there is 50000 users I should check all the rows to find images uploaded by a single user ie; 50000 userid * 20 images/user = 1000000 scans for table rows This will make the system slow and make overload. What I should do to avoid this?

Upvotes: 2

Views: 131

Answers (2)

John Woo
John Woo

Reputation: 263883

create a Schema like this,

CREATE TABLE dataTable
(
    `userid` INT NOT NULL, 
    `username` VARCHAR(50) NOT NULL, 
    `password` VARCHAR(50) NOT NULL, 
    `email` VARCHAR(50) NOT NULL
    CONSTRAINT tb_pk PRIMARY (`userID`),
    CONSTRAINT tb1_uq UNIQUE (`username`)
);

CREATE TABLE pathTable
(
    `userid` INT NOT NULL, 
    `imagepath` VARCHAR(50) NOT NULL, 
    CONSTRAINT tb_fk FOREIGN KEY  (`userID`) REFERENCES dataTable(`userid`)
);

specify that userid of pathTable as a foreign key that references to the certain table's (dataTable) primary key and the server automatically indexed it which will make it faster searching.

Upvotes: 3

Vivek Goel
Vivek Goel

Reputation: 24160

Create index on userid field in second table. Syntax: http://dev.mysql.com/doc/refman/5.0/en/create-index.html

What are the biggest benefits of using INDEXES in mysql?

Upvotes: 1

Related Questions