I have a database table for my website where I have the following columns:
post_id
post_name
post_title
post_body
post_id is the primary key since it is auto-incremental. post_name is unique in nature, as I use it in post permalinks. In my functions that retrieve post data from the database, I use post_name for lookup. For example, I use “… WHERE post_name = $post_name …” in my SQL queries, where $post_name is the post name part of the permalink (http://example.com/post-name/).
My question is should I create a “key” index for post_name column? Does it have any effect on performance/speed?
Yes it probably will speed up that particular query. You can always test it of course
And if you make the index unique, then you don’t have to do a select to see if post_name already exists.
If indexes are used, writing to table (for example inserting rows) is
slower, because in addition to just writing the data, MySQL needs to
handle the index file also. This is an issue usually only if you need
very fast inserts or you need to insert thousands of rows at the same
time and fast.
They might increase query speed or they might not. Basic rule is that if
you are searching some specific key in a table, using indexes in that
field will make query faster, but if you have index in some other field,
it won’t make a difference.
For example if you have a table with 1000000 rows in it, and you want to
make this query:
select id,name from table_x where id=123123;
You will get a lot faster results if you have index in the id field.
On the other hand, if you don’t have index in the id, but only in the
name field, the query will be as slow (or even few cpu ticks slower)
than it would be without indexes at all.
Thank you both for your insights. Since my table will have entries in the range of a few hundreds or so, it seems it won’t make much difference. Also, it seems having the post_id in the URL as well as the post_name, like this forum and some other sites do, and making the query based on the post_id which is the primary key would be the optimal way. Not sure if I want to have the post_id in the URL, I will think more on it.
