MySQL Forums
Forum List  »  Optimizer & Parser

Max Number of Indexes?
Posted by: Conner Hewitt
Date: January 26, 2011 02:07PM

Hi Everyone,

I have an InnoDB table that holds around 7 million rows. It's size is around 1.3 GB and has 4 indexes total, all single column. When pulling ALL data from this table, with the way it's currently set up, we have one query that takes around 50 seconds to complete.

I have another server that is an exact replica of our production server (however this server is only a Pentium 4 compared to a Core i7 on our production server) that is strictly used for testing. If I create an index on the table that is a composite of 4 columns, I can get this query down to around 7 seconds (this is on a much, much slower server too). 7 seconds is something we can deal with when doing these queries, and on queries that are multi-column specific it's able to retrieve 500k+ rows in about 1 second on the slower server as compared to 10-15+ seconds production.

My question is, for virtually every query on the server to be this fast, this 7 million row table will need roughly 12, 3-4 column composite indexes:

1. Will having this many indexes be an issue? 2 of the indexes would contain either varchar(255) and/or tinytext columns, ideally fully indexed (that may be another issue though in and of itself, any advice on this is appreciated also).

2. How well would this scale to say 100 million rows?

3. How much longer could I expect the queries to take as the table grows into the tens of millions?

Thanks and I appreciate any help!

Options: ReplyQuote

Written By
Max Number of Indexes?
January 26, 2011 02:07PM
January 27, 2011 11:13AM
January 29, 2011 10:38PM
February 02, 2011 09:46AM
February 08, 2011 10:41AM

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.