MySQL Forums
Forum List  »  Performance

Re: Indexing question: Help me optimize indexes!
Posted by: James Day
Date: March 27, 2005 09:21PM

(1) Your DBA makes a good point, mostly.

Observation of your actual server load (and how long queries take) can help to identify the indexes which are most useful. Also, think caching when thinking of indexes -are the indexes in RAM? Can they fit in RAM? At high traffic levels I do a lot of thinking about cache efficiency and RAM use.

If and only if you don't have enough RAM for all indexes and can improve cache efficiency you might consider whether the common link_id, private prefix of link_id_3 and alias_3 will narrow down the number of records enough to make it worth eliminating one of them. It's possible that this might increase cache hit rate and overall efficiency, but I doubt it. If your data set is too large to cache even the index, it's even less likely to be the case.

Sometimes you can find different ways to write queries which can make an index unnecessary. Sometimes an operation is so rare that you don't care if a full table scan happens (but if end users can trip it, beware of DOS!).

If updates to the tables are relatively infrequent compared to reads it's easy to worry too much about having many indexes. If this does become a factor, consider InnoDB, since it will cache updates to indexes in its buffer pool and make this much less significant. If you aren't already considering InnoDB, you should, because it has better crash recovery than MyISAM.

(2) Ignore the observations of PhpMyAdmin. They don't apply, the things its observing make sense in your situation.

(3) Both. Where you have UNIQUE, that's to enforce a database integrity or business rule. That's probably why you have some indexes (like link_id) when a prefix on another index can handle the query. The other one can't get the correct UNIQUE rule, so you end up with both.

(4) Has your DBA started telling you to look for "using index" in EXPLAIN SELECT output yet? If not, expect it...:) That's telling you that you're getting efficient and well cached index reads which are dodging the need to read the actual data record to fulfill the query. Can be a massive saver of disk seeks. Called a "covering index". Particularly applicable when many records are involved or the query is extremely common and the retrieval order is very different from the physical order of the records.

(5) While you won't really like this answer, this is based on my own experience tuning a top 100 web site. Believe the DBA. You just won't notice this at low data volumes and traffic levels, because everything will be in cache. At high levels you'll become increasingly desperate for those indexes.

One symptom at low data volumes, with everything in cache, is high CPU use. The caching can mask a lot of the work but the CPU still has to do those table scans and they show up at the CPU.

Get mytop. Watch its output. Log it. Review it. See what's slow. Then try removing an index you think is unnecessary, at a low load time. Be prepared to see your site die of overload. It might. Much better: do this on a database slave you're using for queries, so you don't have a disaster and can recover by taking that slave out of service until you've added the index back.

When the DBA asks you for some change, give it a try without and then with the change. Repeat. Add lots of data and repeat again. Continue to do this until you're confident that the DBA does indeed know how to tune for large data volumes. Chances are, the DBA does.

Options: ReplyQuote

Written By
Re: Indexing question: Help me optimize indexes!
March 27, 2005 09:21PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.