How do you make a composite index (with phpMyAdmin)?

On the PHP forum, I asked about a key for my mammals table. I want it to feature the first three letters of each taxonomic name. For example the key for Carnivora would be car, while Felidae would be fel. The only problem is that there are thousands of scientific names, many of which feature the same first three letters.

Someone suggested I used a composite index, which sounds like a great idea. I have another field which is simply a column of numerals, about 4,800 for the mammals table. Here are examples of some of the codes that might be generated by using both columns:


That will give me unique codes, and I can then strip the numerals out for display purposes. But I can’t figure out how to make a composite index. I’ve been looking at MySQL’s website and some tutorials but haven’t found clear instructions yet.

I know how to create an index in phpMyAdmin. Would I simply create an index on one field, create an index on a second field, then somehow join them into a composite index with phpMyAdmin? And do the fields have to be physically adjacent, or could I create a composite index a table’s first and last fields?


Multi-column index?

ON tbl_name (col1, col2, …)

can you explain what you mean by “i want it [the key] to feature the first three letters of each taxonomic name”

what do you mean by “feature”?

i would just declare an index on the name column

the purpose of an index is to find rows efficiently, so if you have an index on the name field, you can then do stuff like WHERE name LIKE ‘fel%’ and the query will be executed using the index on the name

OK, from square one…

I have a table with a field named FamMamName that includes the name of every mammal order, family, subfamily, genus and species. I just made a second field that features the first three letters of each of these scientific names. Here are some examples:

car | Carnivora
pri | Primates

I want to use these three-letter codes for links and style sheet codes. For example, the Carnivora page might be at, and the tiger page would be at, rather than

I would like to designate the field with the three-letter codes a key, but there are many duplicate entries. For example, Balaenidae and Balaenopteridae (two whale familes) both yield “bal.”

Unfortunately, the names aren’t unique, either. I can’t think of an example offhand, but here’s a hypothetical example:

Canis blanca (the white-haired dog)
Cervus blanca (the white-haired deer)

The genus names are unique, but both species names are “blanca.”

So my scheme won’t work unless I can somehow modify either the names or their three-letter abbreviations so that each one is unique.

I just figured out how to display the three-letter codes, and I put a numerical field right next to it. I can now put it in Dreamweaver and play with the HTML, transforming it into a single column.

So I would start with three fields, like this:

car | 2 | Carnivora
pri | 12 | Primates
bla | 183 | blanca
bla | 4922 | blanca

And wind up with this:

car2 | Carnivora
pri12 | Primates
bla183 | blanca
bla4922 | blanca

But if I can figure out how to make a composite key, I could leave the three-letter codes and numerals in separate columns. It would make it much easier to add or remove species, and it might also be handy if I want to display data from one column or another.

I was hoping you could make a composite key with phpMyAdmin, because I haven’t yet learned how to work with MySQL code. I’ll take a look at another program I have, Navcat. It’s proven itself pretty handy.


okay, i understand what you’re trying to do, but the answer that i come up with is simply to store the URL path for each animal

that way, you can fool around with (i.e. change) the URL scheme all you want, and not have to try to mimic the structure of the URL scheme in the database

but hey, what do i know

by the way, there’s no problem declaring an index on a column that has many duplicates

Something like this?:

Carnivora | <a href=“/car/”><?php echo “$myorder” ?></a>
Felidae | <a href=“/car/fel/”><?php echo “$myfamily” ?></a>

“By the way, there’s no problem declaring an index on a column that has many duplicates.”

I didn’t know that. In fact, that could be kind of handy if I want to check out groups with the same name so I can weed out mistakes.


well, i don’t do php, but i can fake it

what you want to do here instead is this –

Carnivora | <a href=“<?php echo “$orderurl” ?>”><?php echo “$myorder” ?></a>
Felidae | <a href=“<?php echo “$familyurl” ?>”><?php echo “$myfamily” ?></a>

note: the idea i’m trying to give you is that you don’t hardcode the navigation structure in your html, and then try to make the database match it, rather, you store the url for anything as a data value for that thing

that way you can change the url scheme and not have to recode all your pages

Ah, I see. I think your idea merits a closer look. :slight_smile: