Pulling details with wildcard vs narrow mysql query

Question

I'm playing around with a PHP/MySQL. I can't devise what is "better" and why ?

This isn't really a direct technical question but more of a efficiency related question.

I'm trying to pull 1 row from a table, this is the general code:

$query=mysqli_query($con,"SELECT * FROM `tracked_sites` WHERE checkType =1");
while ($row = mysqli_fetch_assoc($query)) {
    echo $row["domain"] . "<br/>";
}

This works and everything is just fine, however I could also do it using a more specific query, instead of using wildcard. I could just specify the domain and it would trim down the results to just that row.

I'm trying to understand what would be the impact (if any) on larger dbs (lets say 10M or 100M items)?

You mean difference between SELECT * and SELECT someColumn,performance wise? — Mihai, Oct 24 '13 at 17:28
If your pulling one row you don't need `while` and perhaps a `LIMIT 1` — AlexP, Oct 24 '13 at 17:31
No, lets say we have N rows for "domain", i still need to use `while` to pull each of them out. i wonder as to the difference between `SELECT * FROM 'tracked_sites' WHERE checkType =1` and `SELECT domain FROM 'tracked_sites' WHERE checkType =1` in terms of efficiency — , Oct 24 '13 at 17:53
See [Why is SELECT * considered harmful?](http://stackoverflow.com/q/3639861) and [Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc](http://stackoverflow.com/q/65512) — eggyal, Oct 24 '13 at 17:56

score 0 · Accepted Answer · answered Oct 24 '13 at 22:20

Reducing the width of the data fetched the database will always improve performance - the DBMS must allocate memory for any data it reads from the disk, it must then transmit that to the client and the client must allocate memory to store it. The more memory is used, he slower a system gets.

However if the difference is only a few kb, then this on its own won't have much impact - but what does have a very big performance impact is when you select data using a covering index.

Thank you, this is actually a more helpful answer as you took the time to explain. — , Oct 27 '13 at 13:50

Pulling details with wildcard vs narrow mysql query

1 Answers1