Re: INDEXING ALL COLUMNS
More details. CREATE TABLE mailer_student_status ( student_id decimal(22,0) NOT NULL default '0', param varchar(128) NOT NULL default '', value varchar(128) default NULL, PRIMARY KEY (student_id,param). KEY idx_value (value) ) SELECT VALUE FROM mailer_student_status WHERE student_id=586925 and VALUE = 0 SELECT VALUE FROM mailer_student_status WHERE PARAM = 'FIRST_MAILER_COUPON_CODE' and VALUE = 0 On Sat, Sep 6, 2008 at 3:04 AM, Aaron Blew <[EMAIL PROTECTED]> wrote: > We'd need more information on what the where clauses of the queries > look like to assist with this. > > -Aaron > > On 9/5/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi, > > > > What would you say about the below table . What can i do to make it more > > efficient. > > > > CREATE TABLE mailer_student_status ( > > student_id decimal(22,0) NOT NULL default '0', > > param varchar(128) NOT NULL default '', > > value varchar(128) default NULL, > > PRIMARY KEY (student_id,param). > > KEY idx_value (value) > > ) > > > > On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> > wrote: > > > >> Hi, > >> > >> Following on from what Mike mentioned, indexing all columns does not > >> really help as MySQL will at most use one index for a query, so its > >> important to pick your indexes carefully and consider constructing > >> composite indexes. An index on a single column may not even be used > >> due to poor cardinality. > >> > >> Ewen > >> > >> On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati > >> <[EMAIL PROTECTED]> wrote: > >> > Hi all, > >> > > >> > I am looking for, is there any specific reason for not indexing all > >> columns > >> > of a table. whats the impact on the performance. Although indexing is > >> meant > >> > for getting great performance. So, why indexing all columns is not > >> > feasible. (Read in docs that all columns should not be indexed) > >> > > >> > -- > >> > Krishna Chandra Prajapati > >> > > >> > > > > > > > > -- > > Krishna Chandra Prajapati > > > > -- > Sent from my mobile device > -- Krishna Chandra Prajapati
Re: INDEXING ALL COLUMNS
Hi, Well at first glance its hard to tell since "param" and "value" don't say a lot about the nature of the data. If this is innodb, you can have a PRIMARY KEY of student_id (assuming its unique) and a separate index on param, this is because of the way innodb is structure, the primary key is always implied in the makeup of any other index. You could perhaps consider how much of param and are interesting and create a composite index on them idx_param_value (param(10),value(10)) or something similar. Whats important for data types is what is going to be held, and whats important for indexes is how the data is going to be queried. Ewen On Fri, Sep 5, 2008 at 10:50 PM, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi, > > What would you say about the below table . What can i do to make it more > efficient. > > CREATE TABLE mailer_student_status ( > student_id decimal(22,0) NOT NULL default '0', > param varchar(128) NOT NULL default '', > value varchar(128) default NULL, > PRIMARY KEY (student_id,param). > KEY idx_value (value) > ) > > On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> wrote: >> >> Hi, >> >> Following on from what Mike mentioned, indexing all columns does not >> really help as MySQL will at most use one index for a query, so its >> important to pick your indexes carefully and consider constructing >> composite indexes. An index on a single column may not even be used >> due to poor cardinality. >> >> Ewen >> >> On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati >> <[EMAIL PROTECTED]> wrote: >> > Hi all, >> > >> > I am looking for, is there any specific reason for not indexing all >> > columns >> > of a table. whats the impact on the performance. Although indexing is >> > meant >> > for getting great performance. So, why indexing all columns is not >> > feasible. (Read in docs that all columns should not be indexed) >> > >> > -- >> > Krishna Chandra Prajapati >> > > > > > -- > Krishna Chandra Prajapati > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXING ALL COLUMNS
We'd need more information on what the where clauses of the queries look like to assist with this. -Aaron On 9/5/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi, > > What would you say about the below table . What can i do to make it more > efficient. > > CREATE TABLE mailer_student_status ( > student_id decimal(22,0) NOT NULL default '0', > param varchar(128) NOT NULL default '', > value varchar(128) default NULL, > PRIMARY KEY (student_id,param). > KEY idx_value (value) > ) > > On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> wrote: > >> Hi, >> >> Following on from what Mike mentioned, indexing all columns does not >> really help as MySQL will at most use one index for a query, so its >> important to pick your indexes carefully and consider constructing >> composite indexes. An index on a single column may not even be used >> due to poor cardinality. >> >> Ewen >> >> On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati >> <[EMAIL PROTECTED]> wrote: >> > Hi all, >> > >> > I am looking for, is there any specific reason for not indexing all >> columns >> > of a table. whats the impact on the performance. Although indexing is >> meant >> > for getting great performance. So, why indexing all columns is not >> > feasible. (Read in docs that all columns should not be indexed) >> > >> > -- >> > Krishna Chandra Prajapati >> > >> > > > > -- > Krishna Chandra Prajapati > -- Sent from my mobile device -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXING ALL COLUMNS
Hi, What would you say about the below table . What can i do to make it more efficient. CREATE TABLE mailer_student_status ( student_id decimal(22,0) NOT NULL default '0', param varchar(128) NOT NULL default '', value varchar(128) default NULL, PRIMARY KEY (student_id,param). KEY idx_value (value) ) On Sat, Sep 6, 2008 at 1:16 AM, ewen fortune <[EMAIL PROTECTED]> wrote: > Hi, > > Following on from what Mike mentioned, indexing all columns does not > really help as MySQL will at most use one index for a query, so its > important to pick your indexes carefully and consider constructing > composite indexes. An index on a single column may not even be used > due to poor cardinality. > > Ewen > > On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati > <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > I am looking for, is there any specific reason for not indexing all > columns > > of a table. whats the impact on the performance. Although indexing is > meant > > for getting great performance. So, why indexing all columns is not > > feasible. (Read in docs that all columns should not be indexed) > > > > -- > > Krishna Chandra Prajapati > > > -- Krishna Chandra Prajapati
Re: INDEXING ALL COLUMNS
Hi, Following on from what Mike mentioned, indexing all columns does not really help as MySQL will at most use one index for a query, so its important to pick your indexes carefully and consider constructing composite indexes. An index on a single column may not even be used due to poor cardinality. Ewen On Fri, Sep 5, 2008 at 9:27 PM, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi all, > > I am looking for, is there any specific reason for not indexing all columns > of a table. whats the impact on the performance. Although indexing is meant > for getting great performance. So, why indexing all columns is not > feasible. (Read in docs that all columns should not be indexed) > > -- > Krishna Chandra Prajapati > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXING ALL COLUMNS
As your table grows your inserts will start to get slower and slower. You run into the issue of locking a table due to re-creating the indexes. Also wasted space for indexes On 9/5/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi all, > > I am looking for, is there any specific reason for not indexing all columns > of a table. whats the impact on the performance. Although indexing is meant > for getting great performance. So, why indexing all columns is not > feasible. (Read in docs that all columns should not be indexed) > > -- > > Krishna Chandra Prajapati >
INDEXING ALL COLUMNS
Hi all, I am looking for, is there any specific reason for not indexing all columns of a table. whats the impact on the performance. Although indexing is meant for getting great performance. So, why indexing all columns is not feasible. (Read in docs that all columns should not be indexed) -- Krishna Chandra Prajapati
