Need to use indexes in SELECT * without using where, How to do that ?
Results 1 to 5 of 5

Thread: Need to use indexes in SELECT * without using where, How to do that ?

  1. #1
    Member
    Join Date
    Sep 2010
    Posts
    80

    Need to use indexes in SELECT * without using where, How to do that ?

    Hello,

    I've a table like that

    Table name : table1

    a_id,a_name,a_code,a_number,a_date,a_type,a_quant,a_by

    And I use indexes in (a_name,a_id (Primary key), a_code,a_number)

    Now I want to make select * from table1 to get all of the data

    However no indexes are use in explain query, How I can tell the query to use index without using the where clause for sure as I don't want to specific the values.

    Also I tried use index and force index and nothing changed

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,906
    There's no point using an index because the entire table will have to be scanned anyway.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    Member
    Join Date
    Sep 2010
    Posts
    80
    I also tried to specific the select query (selecting about 70% of the table) and also no luck, No way to get a key appears in the explain.

    Also tried the force index and use index after specific the query data.

    Any suggestions ? I've made the query more specific by selecting some columns however I also can't get an index to be used in the query.

  4. #4
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,974
    It might help to see some examples of the queries, but as stated in the first reply: if you don't have a where clause, then no indexes will be used. Even if you have a where clause, index usage isn't guaranteed, depending on what is in that where clause, or at least an order by on and indexed column. (E.g.: if your where clause would select 70% of the records, the DBMS may decide it can actually run faster by just reading all the rows anyway, rather than doing index processing.)
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  5. #5
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,906
    Quote Originally Posted by 2CODE
    I've made the query more specific by selecting some columns however
    Columns or rows? Restricting which columns are selected wouldn't make a difference because you'd still be scanning every row in the table.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

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
  •