Indices or no?

gerardg

I've got a typical user info table - email, first name, last name, address, city, state, zip, phone, etc.

The only time indices would be needed on it is from a single report page (probably viewed a few times/day - most of the time just sorted by email) that allows sorts by most of the fields.

Right now I just have indices on the ID and email fields and figure it's not worth it on the other fields, especially since there will be tons more inserts than sorts (on those fields).

So...

Would having indices on all fields really affect insert/update performance?
How "bad" are sorts on fields without an index?

Anon

If it's only viewed a few times a day, it's probably not worth it. But that depends on how many inserts you do and how many entries are in the table... if you're dealing with tens of thousands or more, you will probably notice the insert slowdown.

Anyway, you can always copy the table and run benchmarks off both of them to see the speed difference with and without indices.

[deleted]

Would having indices on all fields really affect insert/update performance?

having an index on all fields is useless because the index would be a 1:1 copy of the original table, and thus be just as slow to use as the original table.

How "bad" are sorts on fields without an index?

indices are not just used for sorting, they are used for everything you do in the WHERE clause. Without indices you force the database to do a sequencial scan (read every single record in the table one by one), the slowest operation known to databases.

I don't think you are going to notice the slowdown on inserts/updates until you are doing thousands of inserts per second.
By the time you notice the slowdown on inserts, you'll have so many records that you'll desperately need indices to make your selects work at an acceptable speed.

gerardg

having an index on all fields is useless because the index would be a 1:1 copy of the original table, and thus be just as slow to use as the original table.<

Are you talking about a multi-column index on all fields? I agree, it probably wouldn't be of much use, but it would be more sorted than the table itself. I'm talking about one index for each field (maybe two fields).

indices are not just used for sorting, they are used for everything you do in the WHERE clause.<

I know 🙂.

[deleted]

"but it would be more sorted than the table itself."

that would save a few milliseconds on ordering, but re-ordering an index for every insert also takes time... 🙂

"I'm talking about one index for each field (maybe two fields)."

A definate no-no. Having an index on every field means first of all that your insert will slow down a fair bit due to all the index updates, but more importantly, the database will have to read several seperate indexes and somehow combine them, kind of a like doing a join. It's probably better to create indices that match your query.

It's tricky stuff, probably best to just try it out with a few good benchmarks.

rogue

having an index on all fields is useless because the index would be a 1:1 copy of the original table, and thus be just as slow to use as the original table.

I don't understand how this can be true. Having an index on every field in the table means you will perform an indexed read on any query that uses the WHERE column <operator> <value(s)>... which means read performance will generally be better.

INSERT and UPDATE performance would definitely take a hit by indexing every column. Doing this means you have to update an index for every column, as well as the table, every time you perform an update or delete.

indices are not just used for sorting, they are used for everything you do in the WHERE clause. Without indices you force the database to do a sequencial scan (read every single record in the table one by one), the slowest operation known to databases.

Indexes aren't used for sorting at all. Sorting is a by-product that sometimes happens when you do indexed reads. The ORDER BY clause in a query should be used for sorting. Indexes aren't used for the ORDER BY column at all.

FULL SCANS are definitely not the slowest read operation in a database... Although it depends on the type of database you are using, obviously... In general if you do an indexed read of a large percentage of your table, you are wasting time, CPU and I/Os. This is because the database has to read the index first, then go read the table itself (Two I/O's FOR EACH ROW!). If you full scan the table, the database reads a boatload of rows for every I/O. That means an indexed read could do (#of_rows * 2) = I/O's... A full scan will do (rowcount/#of_rows per I/O) I/O's. If your table had 50 rows, and your database read five rows per I/O (a GROSSLY CONSERVATIVE number), a full scan will perform 10 I/Os... the indexed read will perform 200 I/Os.

I don't think you are going to notice the slowdown on inserts/updates until you are doing thousands of inserts per second.
By the time you notice the slowdown on inserts, you'll have so many records that you'll desperately need indices to make your selects work at an acceptable speed.

I agree with this, although I guess the amount of performance hit you take depends on the type of data in the table and also on the number of indexes in the table. This is why you don't want to blindly index every column in your table, you want to find those columns that are used for querying, and index THEM. (and ofcourse, this is a pretty broad statement, a lot more thought goes into this decision...)

CSN, I think Brian recommended testing performance to determine the slowdown of inserts, updates and deletes depending on the indexing strategy you come up with. That's a great idea, do it. Also, if you only ever search based on the EMAIL column, that's probably the only index you'll need for your queries. (foreign keys using this table not withstanding).

Hope that helps!

sean

[deleted]

"I don't understand how this can be true. Having an index on every field in the table means you will perform an indexed read on any query that uses the WHERE column <operator> <value(s)>... which means read performance will generally be better."

Note the difference between havind seperate indeices for each column, and having one big index on all columns at once.

An index is just a copy of the original data, neatly sorted to your specifications.

However, if you have an index that contains all the columns of the original table, then your index is the same size as the original table. That means that reading the index takes just about as much time as reading the real table, and thus it is just a slow as doing a sequencial scan.

Having a seperate index on each column might speed things up, but it will require the database to check all indices to see which ones it can use, then load the appropriate indices, merge them, and use them.

"FULL SCANS are definitely not the slowest read operation in a database... "

Uhm... a sequencial scan is not the slowest operation because sometimes using an index makes it even slower? What's your point? 🙂

"This is why you don't want to blindly index every column in your table, you want to find those columns that are used for querying, and index THEM"

Amen to that brother!

gerardg

A definate no-no. Having an index on every field means first of all that your insert will slow down a fair bit due to all the index updates, but more importantly, the database will have to read several seperate indexes and somehow combine them, kind of a like doing a join. It's probably better to create indices that match your query.

It's tricky stuff, probably best to just try it out with a few good benchmarks.<

So, say you had various queries that, in total, used all/most fields for selects and sorts. Even just of bunch of:

select from users order by first_name
select from users order by last_name
select from users order by address
select from users order by state
select * from users order by zip_code
...

You'd surely have several single-column indices, plus maybe some multi-column indices of like email + first/last name.

[deleted]

Well, as Sean Dillon pointed out, indexes are not used for ordering, only for the WHERE clause.

SELECT * FROM table ORDER BY field;
will always cause a sequencial scan, and never use an index, simply because you are selecting all rows.

queries with a WHERE clause can use indices:
SELECT * FROM table WHERE field=value;
Now the engine can load an index that contains the 'field' column and read that to find the records where field=value. ONce found it can open the real table and skip straight to the rows it needs wihtout having to read any of the other rows.

gerardg

indexes are not used for ordering, only for the WHERE clause<

...EXCEPT sometimes when you use offsets and limits. I just verified this with explain - depends on what they are - sometimes it uses all. So, I guess my question still applies - all my 'select ... from ... order by ...' use offsets and limits.

[deleted]

"...EXCEPT sometimes when you use offsets and limits. I just verified this with explain - "

With which queries?

offsets and limits don't use indices, because they are of no use. offset and limit only determine which of the results you see, it still fetches all of them.

Try making a table with 2 million rows, build an index, and do:

SELECT * FROM table LIMIT 1999999,1;

then go grab a cup of coffee... :-)

gerardg

With which queries?

offsets and limits don't use indices, because they are of no use. offset and limit only determine which of the results you see, it still fetches all of them.<

Is Mysql lying to me? 🙂 With:

explain select *
from users
order by Email
limit 25,250

... type=index ... key=Email ... rows=1000 ...

It makes since the query would use the Email index - get entry #offset from the index and go from there to limit.

Playing around with the values, sometimes explain returns:

... type=all ... rows=1000 extra=using filesort

Somewhere in the docs it says (something like) indices aren't used if the estimated resultset is above a certain percentage of total rows.

[deleted]

"Is Mysql lying to me? 🙂"

Not lying, just telling you what it thinks it can do. Databases work out a queryplan that they think will do the job. Sometimes however, they are wrong and either the databases will find out it's solution doesn't work and fallback to a method that does work, or you will be stuck with hopelessly slow queries.

The index just contains data about what's in the rows, and they are only used to find rows that contain certain data.
If you don't want to search for records that contain certain data, then the database will have to select all records, in which case it really doesn't matter how many indices you have.