Is an 'initial' field useful?

[svivian]

In my database I have several tables with a 'name' field. I added an 'initial' field for use on pages where I selected based on the first letter. This was before I leant about indexing.

 

Just wondering if what I have done is worthwhile or if simply indexing the name field would be just as fast?

[roopurt18]

The only way to increase database queries is with an index.  Even if you have an `initial` field, it won't make anything any faster without an index on it.

[aschk]

If you want to index just the first character of a column you can use the following:

CREATE INDEX `<name of index>` ON <table> (<column name>(1));

 

This will create an index on the first letter of the column. Meaning you can find all the names that contain 'F' as their first letter ( for example) faster.

i.e.

SELECT name 
FROM <table>
WHERE RIGHT(name,1) = 'F';

 

OR

SELECT name 
FROM <table>
WHERE name LIKE 'F%';

 

Hope that helps.

[aschk]

Oops, need to make a correction to my post above.

The 1st SELECT statement should be:

SELECT name 
FROM <table>
WHERE LEFT(name,1) = 'F';

 

i.e. change the RIGHT for a LEFT

[svivian]

OK thanks. Two more questions:

1. If I'm already indexing my 'name' field, I don't need to add a second index for the first letter as well, do I?

2. Is there a difference between a normal index and a unique index, besides the unique constraint? In other words, is the unique index still optimised for eg where clauses in the same way?

[aschk]

1) Not unless you're expecting to offer people the option of searching by name first initial, in which case, yes you DO want to index the first letter of the name.

 

2) The difference as you suggested between a normal INDEX and a UNIQUE INDEX is indeed the uniqueness, however think about this logically.

If the value is UNIQUE then there is only 1 of the value in the WHOLE table EVER! So as far as MySQL is concerned if you ask it for a particular name that has a UNIQUE INDEX on it, then it knows EXACTLY where that is, and will only ever examine 1 row.

However, if you have a normal INDEX there is the possibility that you're looking at more than 1 row (because non-unique means there can be more than 1). Meaning MySQL will probably expect to have to examine more than 1 row.