I have a table with about 65 fields, and out of 65 fields abut Half need INDEX as there are a lot of data, and we need to have search facility on those 30 fields.
The data in the 28 fields (Varchar size 1) are like “1” and “0” and 2 fields are date field and I want to all these indexed.
since you did not share the table design with us, i’m gonna make up an example
let’s pretend that these columns are called something like this --[indent] likes_dogs, likes_kids, owns_car, owns_jet, previously_married, …[/indent]
remove these columns from your table
(indexing them wouldn’t help your query performance anyway, because of high cardinalities)
But what if I index it ?
There will be like 5000 Rows after 1 year. 10000 in 2 years, and then I can put a code in my PHP for Showing the current Records, and Showing the past Records. Will that be ok ?
A few billion rows presents MANY problems! The table doesn’t fit in memory anymore, may not even fit on disk anymore, maintaining indexes can become unacceptable overhead…
Read Rudy’s advice carefully as it’s important. If most of your columns are just 0/1 values, then an index is not likely to help (and unlikely to even be used), but a better database design may.