Suggest proper table design please
Posted by: m newbie
Date: June 04, 2012 03:25PM

I have one table (MyISAM) contains around 10 millions of records with around 30% of fields with enable/disable factor and my most of the select queries filters (where condition) records set on those columns which yields full table scan and full index scan on very low cardinality columns ultimately very very slow read queries.

For Example:
CREATE TABLE usersinfo (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
comments TEXT DEFAULT NULL,
act_approved ENUM ('Y','N') DEFAULT 'N',
pkgtype TINYINT UNSIGNED NOT NULL DEFAULT '1' COMMENT '1=Free, 2=Medium, 3=Large',
countryname CHAR(4) NOT NULL,
validemail ENUM ('Yes','No'),
usertype TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '1=buying, 2=selling',
userrating ENUM ('OK','GOOD') DEFAULT 'OK'
UNIQUE INDEX (username_idx),
INDEX acc_approved_idx (acct_approved),
INDEX pkgtype_idx (pkgtype),
INDEX countryname_idx (countryname),
INDEX validemail (validemail_idx),
INDEX usertype (usertype_idx)
INDEX userrating (userrating_idx)
ENGINE=MyISAM);

Data Distribution:
acct_approved: Y=90%, N=10%
pkgtype: Free=90%, Medium=5%, Large=5%
countryname: USA=75%, Rest of the World=25%
validemail: YES=90%, NO=10%
usertype: buying=40%, selling=60%
userrating: OK=95%, GOOD=5%

Following SELECT queries i have (WHERE condition is most important)
1) SELECT * FROM usersinfo WHERE acct_approved = 'Y';
2) SELECT * FROM usersinfo WHERE pkgtype = 'Free' AND userrating = 'OK';
3) SELECT * FROM usersinfo WHERE countryname = 'USA'AND validemail = 'Yes';
4) SELECT * FROM usersinfo WHERE usertype = '1' AND userrating = 'Good';

Now you can see all those queries searches through poor index cardinality column and as per data distribution searching on highest percantage of rows.
I thought the only way i can improve all SELECTs is by creating another reference table and move all high cardinality column to that reference table (e.g. acct_approved, pkgtype, usertype etc..) and SELECT them via joining or may be something else.

Can someone please provide suggestion for proper table for that purpose ?
MySQL Version i am using 5.1.

Options: ReplyQuote


Subject
Written By
Posted
Suggest proper table design please
June 04, 2012 03:25PM


Sorry, you can't reply to this topic. It has been closed.

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.