MySQL Forums
Forum List  »  Optimizer & Parser

Re: Max Number of Indexes?
Posted by: Rick James
Date: January 27, 2011 11:13AM

The answers: "It depends."

There are no simple answers because there are many factors interacting in various ways. Let's dig into some specifics to try to get some answers. First, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have on each machine?

innodb_buffer_pool_size is a big factor on speed, because it factors into whether you are I/O bound versus CPU-bound. 50sec vs 7sec smells a lot like I/O-bound vs CPU-bound. Or it could be an artifact of how you did the timing...

On a cold system (nothing cached), it might take 50 seconds to read a big table. Read the same table in the identical manner again, and it may take 7 seconds. This would be because of caching. Run any benchmark things twice in a row.

The types of fields in the indexes, the relative size of the index versus the full data row has some impact. What's the average length of your VARCHARs? (Names tend to be short; urls tend to be long.)

Why are you pulling ALL the data? 7M rows is a lot to digest. Do you even need a database? Why not a plain file?

But even reading 7M lines from a file will take time. Disks are not instantaneous. Nor are CPUs. 100M rows will be 14 times as slow; possibly worse because of less chance to cache things.

Is the 50s (or 7s) really the bottleneck? How long does it take you to process them?

Separate indexes for each column is rarely the wise thing to do. But it depends on the queries. Actually, we don't know if any index was used in your query! (The EXPLAIN will answer that question.)

Options: ReplyQuote

Written By
January 26, 2011 02:07PM
Re: Max Number of Indexes?
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.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.