CODEWITHSUNDEEP
phpmysql
Rockstar
Rockstar

Reputation: 191

performance of MYSQL and PHP fetching records

Scenario 1

I have one table lets say "member". In that table "member" i have 7 fields ( memid,login_name,password,age,city,phone,country ). In my table i have 10K records.i need to fetch one record . so i'm using the query like this

mysql_query("select * from member where memid=999");

Scenario 2

I have the same table called "member" but i'm splitting the table like this member and member_txt .So in my member_txt table i have memid,age,phone,city,country )and in my member table i have memid,login_name,password .

Which is the best scenario to fetch the data quickly? Either going to single table or split the table into two with reference?

Note: I need to fetch the particular data in PHP and MYSQL. Please let me know which is best method to follow. we have 10K records

Upvotes: 2

Views: 1354

Answers (3)

Tower
Tower

Reputation: 102755

For your own health, use the single table approach.

As long as you are using a primary key for memid, things are going to be lightning fast. This is because PRIMARY KEY automatically assigns an index, which basically tells the exact location for the data and eliminates the need to go through data that it would otherwise do.

From http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Your second approach only makes your system more complex, and provides no benefits.

Use the scenario 1.

Upvotes: 2

Ben
Ben

Reputation: 355

In general you should not see to much impact on performance with 10k rows as long as your accessing it by your primary key.

Also note that fetching data from one table is also faster than fetching data from 2 tables.

If you want to optimize further use the column names in the select statement instead of the * operator.

Upvotes: 0

Suleman Ahmad
Suleman Ahmad

Reputation: 2093

please make the memid primary/unique key then having one table is faster than having two tables.

Upvotes: 0

Related Questions