Which is more efficient?

Anon

I am displaying an alphabetical listing of all entries in a database to a webpage. But they are seperated by the letter heading. ie:
[A]
item 1
item 2
item 3

item 1
item 2
so therefore, a single call to the database and the results just displayed continuously cannot be used, as the letter heading needs to be placed between each listing. Which would be more efficient and less of a load (quicker response time) on the server?

Method 1:
using:
- display heading
- $result = mysql_query("SELECT * FROM database WHERE title LIKE 'a%' ORDER BY title",$db);
- display contents
- repeat to z
for each letter.. ie a total of 26 calls to the database.

OR

Method 2:
one call to the database using
- $result = mysql_query("SELECT * FROM database ORDER BY title",$db);
- display 'a' letter header
- while substr($title, 1) == a
display 'a' listing
- display 'b' letter header
- while substr($title, 1) == b
display 'b' listing
- display 'c' letter header... and so on until letter z

I believe Method 2 would be the most efficient.

Another other suggestions on how it can be done??

Thanks!

brandonschnell

grab all of the entries from the db. then in php loop through each entry.

$current_letter_heading = strtoupper($row->title[0]);

if ($previous_letter_heading != $current_letter_heading)
{
echo "[".$current_letter_heading ."]<BR>\n";
$previous_letter_heading = $current_letter_heading;
}
echo $row->title . "<BR>\n";

of course this is pseudo code. but since the result is already sorted and all you need to check is the first char, it should be fast. just make sure it ignores case, and upper case the letter headers.

Anon

Thanks Brandon!!

It works great!

Anon

You can skip the sql ordering alltogether, and use PHP to organize your data. Query the db as
"SELECT * FROM database"
Then go through you result set like this:

$rows = Array();
while($row = mysql_fetch_assoc($result));
$rows[substr($row['title'], 0, 1)][] = $row;
}

After this process has finished you will have an array with keys A-Z (assuming there are actually at least one entry for each letter, and you can easily print out each section.

Furthermore, you could tweak the DB to contain a field named firstletter or something which simple is the first letter in the title, so that you don't have to call substr() so often.

Anon

Would having PHP organize the words be more efficient or would MySQL be better at it (it's alot easier to execute)?

Anon

I don't know what's fastest. The speed difference will most likely be undetectable unless the database contains an incredible amount of rows. I think my solution gives you a clean and simple script. You're gonna have to go through the resultset at least once, so why not structure it into an array as you go? This also makes it easier to separate the data from the presentation -- first you organize your data, then you present them.

The good thing about PHP is that it's fast, so fast that in most cases you should care more about writing clean and organized code rather than extremely optimized code.

Anyway, based on your method 2 I guess you can do something like this:

$result = mysql_query("SELECT * FROM database ORDER BY title");
$current = 'A';
$current_code = 65; // ASCII Code for 'A'
// Echo letter heading for A here.
while($row = mysql_fetch_assoc($result)) {
if(strtoupper(substr($row, 0, 1)) != $current) {
$current = chr($current_code++);
// Echo letter heading for the new $current here
// Echo data in $row here
} else {
// Echo data in $row
}
}

This way you take advantage of the ASCII codes and the ordering done by MySQL.

brandonschnell

In almost all cases mysql will be faster than php in ordering rows, more so if the rows are properly indexed. At best php would have to create yet another array which takes time then apply a quick sort to guarantee the order of the rows. However mysql can have most of the data already sorted because of the index on the column.

why create another array when the record set in itself returns an array of chars? why call substr() when you can directly access the first char from the record set by referring to the offset in the array of chars returned by the record set (at least with recent versions of php)? why refer to the current letter heading by ascii code when its not needed?

yah gotta admit, my way is a lot more elegant. it'll handle more rows faster and with less lines of code.

Anon

Doing something in few lines of code is not an absolute good, just like writing very short variable names saves you time in the short run, but rears it's ugly head when you actually try to decipher your own code at a later stage. What I was outlining was a solution which would simplify the separation of code/data and presentation, this is one of the major challenges when writing larger PHP applications, and a nicely structured application is an elegant application.

I'm well aware that doing any sort of sort in PHP would be a waste of resources, but remember, this is why I'm using the associative array. I don't know how fast associative arrays are, so I can't say how bad my first solution is compared to my implementation of your and Clint's pseudo code, but I'm quite sure the difference is minimal for small cases (and you don't want to print out more than a few thousand records to one single page at most anyway).

So, to sum up. Always go for neatly organized code, separating data and presentation and building an application consisting of reusable and low-maintenance parts, then optimize time-critical parts of the system if needed, this is what you learn from Software engineering, and for a good reason -- it's an efficient way to work.

brandonschnell

if you compare the examples that we both posted you'll see that they both function in a similiar fashion. an array/record set is created, characters are compared, and the letter title headers are returned. however, the example you posted used a method that was unnecessarily complex and had several possible bugs. For example it'll have problems handling other character sets as its hardcoded for ASCII, it relies on sorts being done using ASCII values, and may not handle non alphanumeric values very well.

Anon

Thank you (André and Brandon) again for your helpfull comments and discussion.