Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Index has to be leftmost prefixes in order to work!


    Hello;
    Can someone please explain this?
    Code:
    SELECT * FROM tbl_name WHERE col1=val1;
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
    
    SELECT * FROM tbl_name WHERE col2=val2;
    SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
    If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).
    Great
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    recall your white pages telephone book

    it has a "clustered index" (i.e. in sequence by) last name, then first name, then address

    if you know a person's last name and first name, you can look them up pretty easily

    if you know only their address, you have to do a table scan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    recall your white pages telephone book

    it has a "clustered index" (i.e. in sequence by) last name, then first name, then address

    if you know a person's last name and first name, you can look them up pretty easily

    if you know only their address, you have to do a table scan
    I might need to create indexes with the same columns, but with different order then! Oh! Application is so big, so many indexes needed like this!
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by zxcvbnm
    I might need to create indexes with the same columns, but with different order then! Oh! Application is so big, so many indexes needed like this!
    Don't go nuts and create an insane amount of indexes now.

    Clustered indexes are great for performance, but as with all indexes you should consider a balance, because if you create a lot of indexes then these compete about disk space, RAM usage, etc, etc so they can actually slow down the entire application.

    So sometimes a single column index might be better than a clustered one since it occupies less RAM and is smaller in size which means an index scan is faster.

    Optimizing an application is about balance, having a lot indexes doesn't mean that the application will be fast, as well as having no indexes at all won't give you performance either.

    So create indexes wisely and with a bit of caution.

    I'm just saying this because it sounded like you where going to start creating indexes yesterday.
    /Stefan
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Hey thanks for the replies.

    In this simple example , how can I get rid of the type All on Country table? The manual says
    This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.
    Code:
    CREATE TABLE `City` (
     `ID` int(11) NOT NULL AUTO_INCREMENT,
     `Name` char(35) NOT NULL DEFAULT '',
     `CountryCode` char(3) NOT NULL DEFAULT '',
     `District` char(20) NOT NULL DEFAULT '',
     `Population` int(11) NOT NULL DEFAULT '0',
     PRIMARY KEY (`ID`),
     KEY `CountryCode` (`CountryCode`),
     KEY `CountryCode_index` (`CountryCode`),
     CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
    Code:
    CREATE TABLE `country` (
     `Code` char(3) NOT NULL DEFAULT '',
     `Name` char(52) NOT NULL DEFAULT '',
     `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
     `Region` char(26) NOT NULL DEFAULT '',
     `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
     `IndepYear` smallint(6) DEFAULT NULL,
     `Population` int(11) NOT NULL DEFAULT '0',
     `LifeExpectancy` float(3,1) DEFAULT NULL,
     `GNP` float(10,2) DEFAULT NULL,
     `GNPOld` float(10,2) DEFAULT NULL,
     `LocalName` char(45) NOT NULL DEFAULT '',
     `GovernmentForm` char(45) NOT NULL DEFAULT '',
     `HeadOfState` char(60) DEFAULT NULL,
     `Capital` int(11) DEFAULT NULL,
     `Code2` char(2) NOT NULL DEFAULT '',
     PRIMARY KEY (`Code`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    In this simple example , how can I get rid of the type All on Country table?
    it's pretty difficult to make any recommendations without seeing the query that produced that EXPLAIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    it's pretty difficult to make any recommendations without seeing the query that produced that EXPLAIN
    Code:
    EXPLAIN SELECT * FROM city INNER JOIN country ON city.countrycode = country.code
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Code:
    EXPLAIN SELECT * FROM city INNER JOIN country ON city.countrycode = country.code
    okay, i thought it might be this

    the optimizer figures out that it has to read all countries, since there's no limiting WHERE condition

    so using an index would be less efficient than a table scan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Just to keep all the indexing questions here I dont start a new thread just yet. I appreciate if anyone answers this one:

    So is it true to claim that sometimes indexing has no effect on performance at all because the table scan is neccessary? For example in this case
    KEY `production_date` (`production_date`) is pointless in this query.
    Code:
    SELECT Count(*) AS counter,
           production_date
    FROM   products_table
    GROUP  BY production_date
    ORDER  BY counter DESC
    Code:
    CREATE TABLE `products_table` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `code` varchar(4) NOT NULL,
     `class` varchar(11) NOT NULL,
     `category` varchar(5) NOT NULL,
     `price` decimal(6,2) NOT NULL,
     `production_date` date NOT NULL,
     PRIMARY KEY (`id`),
     KEY `production_date` (`production_date`)
    ) ENGINE=MyISAM AUTO_INCREMENT=900121 DEFAULT CHARSET=latin1
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    So is it true to claim that sometimes indexing has no effect on performance at all
    absolutely true, given that you've used the adverb sometimes

    in the case of your COUNT(*) query with GROUP BY, my guess is that it will definitely be used

    for that query, the index is a covering index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    okay, i thought it might be this

    the optimizer figures out that it has to read all countries, since there's no limiting WHERE condition

    so using an index would be less efficient than a table scan
    Hi;

    INNER JOIN: the query will retrieve only countries that have city associated to them, and cities that have counry associated to them! In other words the query is not a LEFT OUTER JOIN so not all countries will be listed.

    A countries full table scan shouldn't be neccessary because the key countryCode can be used to avoid the full table scan. Why does it have to go through ALL countries? What is the point of that foreign key in regards to indexing then?

    So I thought it goes through the list of cities (all of them), and using foreign key countryCode, it looks for that specific country without needing for full table scan.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Why does it have to go through ALL countries?
    maybe because there are fewer countries than cities?

    perhaps it anticipates that not all cities will have a country?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    maybe because there are fewer countries than cities?

    perhaps it anticipates that not all cities will have a country?
    Ok let me ask it another way: Why doesn't it use the index countryCode on country table?
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by zxcvbnm
    Ok let me ask it another way: Why doesn't it use the index countryCode on country table?
    country table does not have that index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    country table does not have that index
    No doubt you are right, but I see above Country table has
    PRIMARY KEY (`Code`) and city table has countryCode foreign key.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo