#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

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

    Preparing for testing "poor performance"


    Hello;

    I am experimenting with indexes. I have a table now and filled it with 3 million rows with random dates. I was hoping for some poor performance when I tried the query below so I can add indexes and see how they work
    Code:
    SELECT *
    FROM   allotments_new
    But it works in less than a second: Showing rows 0 - 29 (233,513 total, Query took 0.0011 sec)How many records do I need to insert to experiment poor performance on a table like this?

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    this query will never use any indexes

    see if you can figure out why

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    A
    Code:
    SELECT *
    FROM someTable
    doesn't benefit from using index.

    Indexes are used for two things:
    1. Finding specific records (WHERE clause)
    2. Or when you need records in a specific order (ORDER BY (and the way GROUP BY works internally it is used for this also)).

    So the simplest query that will use an index is:
    Code:
    SELECT *
    FROM someTable
    WHERE someColumn = 'something'
    Then learn to use EXPLAIN ... and learn to read the output (it's a bit hard to begin with, but you need it to be able to work with optimizations).

    Optimizations are fun
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo