How to have more than 16 Columns Indexed ?

[zeeshanhashmi]

Hi

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.

Please help me how to solve the issue.

Thanks
Zeeshan

[Dan Grossman]

The maximum number of indexes per MyISAM table is 64. Which storage engine are you using?

[r937]

The data in the 28 fields (Varchar size 1) are like "1" and "0"

this is a big problem, but easily solved

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

likes_dogs, likes_kids, owns_car, owns_jet, previously_married, ...

remove these columns from your table

(indexing them wouldn't help your query performance anyway, because of high cardinalities)

create a new table as follows:

CREATE TABLE properties
( main_id INTEGER NOT NULL 
, property VARCHAR(99)
, PRIMARY KEY ( main_id, property )
, yes_or_no TINYINT
);

get the idea?

[zeeshanhashmi]

okay, yes i get the idea !

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 ?

[felgall]

There will be like 5000 Rows

A few billion rows isn't a problem. Databases are designed to be able to handle lots of rows.

[Dan Grossman]

A few billion rows isn't a problem. Databases are designed to be able to handle lots of rows.

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

okay, yes i get the idea !

But what if I index it ?

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.