How to Avoid Complete Table Scan in Database?

Question

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?

You will need to use indexes. How to design them varies depending on your database structure. — Joachim Isaksson, Oct 07 '12 at 06:48
hm... I heard about database indexes, but don't know how to use it. Can u please give be a good tutorial link for that? — Afsal Meerankutty, Oct 07 '12 at 07:15

John Woo · Answer 1 · 2012-10-07T07:08:22.190

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

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?

edited May 23 '17 at 11:58

Community

1
1

answered Oct 07 '12 at 06:48

Vivek Goel

22,942
29
114
186

How to Avoid Complete Table Scan in Database?

2 Answers2