Is there a better way to create an alphabetic pagination index in PHP/MySQL?

I presume it's a varchar field, so have you considered the following:

SELECT DISTINCT SUBSTRING(lastname FROM 1 FOR 1) FROM mytable;

This will get you a distinct list of the first letters of the last name.

You can also use UPPER() to ensure you just get upper case characters. LEFT() will also achieve something similar, so you should experiment to see which performs quickest on your dataset.

Edit: If you also want counts:

SELECT DISTINCT SUBSTRING(lastname FROM 1 FOR 1) AS firstletter, COUNT(*) AS counter FROM mytable GROUP BY firstletter;

No need to do a second query for each letter.

$sql = "SELECT left(name, 1) AS firstchar FROM mytable ORDER BY name";$result = mysql_query($sql) or die(mysql_error());$letters = array();$row = 0;while($row = mysql_fetch_assoc($result)) {    $row++;    if (!isset($letters[$row['firstchar']])) {        $letters[$row['firstchar']] = $row;    }}

This would give you an array keyed by the first letters, and the row number they first appeared on for the value:

a => 1,b => 50,c => 51,

etc...

There's probably some way of doing it purely in SQL, but MySQL itself doesn't have any 'row number' support built in, so it'd be a highly ugly query.

Just like on standrd pagination is just a matter of fetching and ordering - simply add WHERE with A% (dont forget to create index on this column)