Re: Max Number of Indexes?

[Rick James]

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.)