CODEWITHSUNDEEP
mysqlsql
Sourav
Sourav

Reputation: 17530

Optimizing select SQL - MySQL

Plz help me on this

Table Structure

ID(primary key) Text

To find a text what should i write ?

select Text from table where ID=1

or 

select Text from table where ID=1 limit 1

Another Table Structure

Parent Table
PID(p k) ID

Child Table
ID (p.k) PID(f.k)

what should i write to delete fast ?

delete from ChildTable where ID=1;

or

delete from ChildTable where ID=1 and pid=1;  [i know the PID]

Upvotes: 0

Views: 85

Answers (2)

sh_kamalh
sh_kamalh

Reputation: 3901

Just use the queries with the primary key only.

Upvotes: 1

squawknull
squawknull

Reputation: 5191

The first two statements are equivalent. It should never return more than one row, so adding limit 1 is redundant.

For the deletes, it shouldn't matter. Mysql should look up by ID first as it's the most efficient index. There should only be one row returned, and hence the PID column is redundant, and at worst could actually confuse mysql into using a less efficient query plan.

Upvotes: 1

Related Questions