www.webdeveloper.com
Results 1 to 5 of 5

Thread: Speed of query

Hybrid View

  1. #1
    Join Date
    Mar 2006
    Posts
    413

    Speed of query

    I have a table with 200,000 records.

    it has five fields:
    ref
    strSearch
    strDate
    strSuccess
    website

    it is used to monitor searches on multiple websites.

    I want to return all the terms in a single query and have them presented in a table, ALL 200,000 records.

    It works but it takes an absolute age, about 1min20sec

    I'm not an expert in sql, but the query i have to retrieve the records is:

    Code:
    Select * From tblSearch Where tblSearch.strDate Like '" & strQueryDate & "'"
    Is there any way of speeding this up? i looked at indexes and created one on strDate but i'm not sure if it did anything. my explain looks like:

    id: 1
    select type: simple
    type: all
    extra: using where

    all other fieilds are empty.

    thanks for any help guys

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    use some of the built in optimization tools or pick up a book on optimization. try rebuilding indexes on that table to see if it helps too.

  3. #3
    Join Date
    Mar 2006
    Posts
    413
    what optimization tools, where do i find those?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    they vary from dbms to dbms. look into toad, it has a freeware version. you can also look at the plan for a query (can't be more specific, it'll vary depending on what database you use.)

  5. #5
    Join Date
    Jul 2004
    Location
    West Coast, Canada
    Posts
    665
    is strDate indexed? and I agree with Chazzy, rebuild your indexes.

    This adds an index to a column.
    Code:
    ALTER TABLE tableName ADD INDEX(columnName);
    This repairs your index.
    Code:
    REPAIR TABLE tbl_name QUICK;
    You can type this in your command prompt when you log into a mysql terminal.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles