SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not possible to 2 indexes on a table = Slow query please help?

    Hello you gorgeous person,

    I have a query on 4 tables (via various joins).
    MYSQL automatically uses uses primary keys for the joins, so that part is superb.

    So for example, TABLE A uses A.id primary key as index for the join.

    However, because of this,
    if I do an ORDER BY DATE on table A, MYSQL resorts to using filesort. (EXTREMELY SLOW!!!), because (if im not wrong?) u can't use more than 1 index per table.

    THis happens even though the DATE column is indexed.

    Is there any workaround for this via unions or subselects or something?

    Appreciate any advice on which direction i should head.

    Many thanks!

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2005
    Location
    Wonderful Malaysia
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use more than one index per table....

    There are 4 type of index in Mysql;

    U can use primary key only in one column on one table,

    U can use unique only one column form one table but you can insert null for that column.(primary key can't insert null).

    try to use prepare statement.May be it make your query faster.

    Try to figure out the network connection between your place and the server.
    'Only Paranoid Will Survived'

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    add an EXPLAIN before your query and post the results here. also post teh CREATE TABLE's for your tables.


Bookmarks

Posting Permissions

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