RE: This is confusing..?
I seem to remember someone saying that if a query would end up returning more than x% of a table (I think it was either 30% or 50%), then mysql just ends up doing a full scan regardless... -Original Message- From: Eric Anderson [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 2:08 PM To: [EMAIL PROTECTED] Subject: This is confusing..? Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g ++--+---+--+-+--+- ---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+- ---++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+- ---++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Eric Anderson [mailto:[EMAIL PROTECTED] --Sent: Thursday, November 06, 2003 11:08 AM --To: [EMAIL PROTECTED] --Subject: This is confusing..? -- -- --Given the following table: -- --CREATE TABLE campaign_t ( -- acct_id int(11) unsigned NOT NULL default '0', -- site_id tinyint(3) unsigned NOT NULL default '0', -- ref_id int(11) unsigned NOT NULL default '0', -- datestamp char(10) NOT NULL default '', -- raws int(11) unsigned NOT NULL default '0', -- uniques int(11) unsigned NOT NULL default '0', -- trial_signups int(11) NOT NULL default '0', -- full_signups int(11) NOT NULL default '0', -- annual_signups int(11) unsigned NOT NULL default '0', -- PRIMARY KEY (acct_id,site_id,ref_id,datestamp), -- KEY acct_id (acct_id), -- KEY site_id (site_id), -- KEY ref_id (ref_id), -- KEY datestamp (datestamp) --) TYPE=MyISAM; -- --How come it doesn't use the 'datestamp' index on this query: -- --mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g --++--+---+--+-+--+ +--- ---+ --| table | type | possible_keys | key | key_len | ref | rows | --Extra | --++--+---+--+-+--+ +--- ---+ --| campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | --where used | --++--+---+--+-+--+ +--- ---+ --1 row in set (0.00 sec) -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
At 11:08 -0800 11/6/03, Eric Anderson wrote: Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g Possibly because datestamp and 20041105 have different datatypes. There's an implicit data conversion required for the comparison, which in some cases prevents use of an index. Try writing '20041105' rather than 20041105 and see if that makes a difference. ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+++ 1 row in set (0.00 sec) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. What's the syntax for 'show keys'? -- -- Eric Anderson - ICQ: 3849549 - San Diego, CA [EMAIL PROTECTED] | Mac and Bumble | http://www.macandbumble.com [EMAIL PROTECTED] | VIP BumbleCash | http://vip.bumblecash.com [EMAIL PROTECTED] | Online Net-Entertainment | http://www.on-e.com -- You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g Try surrounding the value with quotes, i.e.: explain SELECT * FROM campaign_t WHERE datestamp '20041105' -- Eric Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
Possibly because datestamp and 20041105 have different datatypes. There's an implicit data conversion required for the comparison, which in some cases prevents use of an index. Try writing '20041105' rather than 20041105 and see if that makes a difference. Nope.. :-/ mysql explain SELECT * FROM campaign_t WHERE datestamp '20041105'\g ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438473 | where used | ++--+---+--+-+--+++ I think someone's onto something though regarding the 'optimize' command. The above selection is actually a DELETE, and it results in about a 3% deletion of the table per day. This has been running for months (if not years) with no optimization. Is it possible that this table is so fragmented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
On Thu, Nov 06, 2003 at 11:08:03AM -0800, Eric Anderson wrote: Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+++ 1 row in set (0.00 sec) If most of the rows match that WHERE clause, it'll just scan the table rather than using the index. It's faster to do so. This is documented in the manual. http://www.mysql.com/doc/en/How_to_avoid_table_scan.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,006,826,779 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. mysql show index from campaign_t\g +++---+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++---+--+-+---+-+--++-+ | campaign_t | 0 | PRIMARY |1 | acct_id | A | 1898 | NULL | NULL | | | campaign_t | 0 | PRIMARY |2 | site_id | A | 5621 | NULL | NULL | | | campaign_t | 0 | PRIMARY |3 | ref_id | A | 109627 | NULL | NULL | | | campaign_t | 0 | PRIMARY |4 | datestamp | A | 438511 | NULL | NULL | | | campaign_t | 1 | acct_id |1 | acct_id | A | 1898 | NULL | NULL | | | campaign_t | 1 | site_id |1 | site_id | A | 36 | NULL | NULL | | | campaign_t | 1 | ref_id|1 | ref_id | A | 54813 | NULL | NULL | | | campaign_t | 1 | datestamp |1 | datestamp | A | 76 | NULL | NULL | | +++---+--+-+---+-+--++-+ I have no idea how to interpret this.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
Hi Eric, The Cardinality of the datestamp column is only 76. That means it thinks there's only 76 unique values in all of your rows. In other words, there's a good chance that your WHERE matches more than ~30% of the rows, in which case the index won't be used. If you haven't run OPTIMIZE in years, it'd probably be a good idea to do that anyway... see if it changes anything. OPTIMIZE includes ANALYZE by the way. On a side note, you might as well remove the separate acct_id index since it's the first column of the PRIMARY KEY anyway where it can be used as if it was a separate index. The additional index is just wasting space. Drop this index before you run the OPTIMIZE. :-) ALTER TABLE campaign_t DROP INDEX acct_id; OPTIMIZE TABLE campaign_t; Matt - Original Message - From: Eric Anderson Cc: [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 1:45 PM Subject: RE: This is confusing..? Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. mysql show index from campaign_t\g +++---+--+-+ ---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++---+--+-+ ---+-+--++-+ | campaign_t | 0 | PRIMARY |1 | acct_id | A |1898 | NULL | NULL | | | campaign_t | 0 | PRIMARY |2 | site_id | A |5621 | NULL | NULL | | | campaign_t | 0 | PRIMARY |3 | ref_id | A | 109627 | NULL | NULL | | | campaign_t | 0 | PRIMARY |4 | datestamp | A | 438511 | NULL | NULL | | | campaign_t | 1 | acct_id |1 | acct_id | A |1898 | NULL | NULL | | | campaign_t | 1 | site_id |1 | site_id | A | 36 | NULL | NULL | | | campaign_t | 1 | ref_id|1 | ref_id | A | 54813 | NULL | NULL | | | campaign_t | 1 | datestamp |1 | datestamp | A | 76 | NULL | NULL | | +++---+--+-+ ---+-+--++-+ I have no idea how to interpret this.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
