Printable View
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
The maximum number of indexes per MyISAM table is 64. Which storage engine are you using?
this is a big problem, but easily solvedQuote:
Originally Posted by zeeshanhashmi
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:get the idea?Code:CREATE TABLE properties
( main_id INTEGER NOT NULL
, property VARCHAR(99)
, PRIMARY KEY ( main_id, property )
, yes_or_no TINYINT
);
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 ?
A few billion rows isn't a problem. Databases are designed to be able to handle lots of rows.Quote:
Originally Posted by zeeshanhashmi
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...Quote:
Originally Posted by felgall
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.Quote:
Originally Posted by zeeshanhashmi