Announcement

Announcement Module
Collapse
No announcement yet.

Howto increase performance???

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Howto increase performance???

    Hello,

    I'm using a zip_employee table (33k records) defining per zip-code range (zipfrom, zipto) the employee who is responsible for this area, something like this:

    CREATE TABLE zip_emp
    ( `zipfrom` char(10) NOT NULL,
    `zipto` char(10) NOT NULL,
    `empid` CHAR(10) NOT NULL
    PRIMARY KEY (`zipfrom`,`zipto`)
    );

    When I'm executing a simple query like the one beneath, the performance is very, very slow:

    select sta.statid, zip.empid
    from `statistics` sta,
    `zip_emp` zip
    where sta.zip between zip.zipfrom and zip.zipto

    Can someone tell me why a query using a from-to join is so slow (a "normal" query is very fast) and what is the alternative?

    Thanks,

    Menrone

  • #2
    What is the table structure of the statistics table, and how many records are in it? If I'm not mistaken, your query will do a cross join of the two tables (basically joining every record of zip_emp to every record of statistics), and then do a table scan through that set.

    Comment


    • #3
      Hello dsuehrin,

      the structure of the the statistics table is:

      CREATE TABLE IF NOT EXISTS `statistics` (
      `id` int(11) NOT NULL auto_increment,
      `zip` char(10) NOT NULL default '',
      `paperid` char(10) NOT NULL default '',
      `freqid` char(10) NOT NULL default '',
      `mdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `MDATE` (`mdate`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=39979 ;

      At the moment it has 12K records.

      Comment


      • #4
        hi,
        Could you give use the explain plan, may be you should just create new index or modify the primary key

        Comment


        • #5
          Hello,

          the result of the explain is :
          id select_type table type possible_keys key key_len ref rows Extra
          1 SIMPLE sta ALL NULL NULL NULL NULL 13074
          1 SIMPLE zip ALL PRIMARY NULL NULL NULL 38065 Range checked for each record (index map: 0x1)

          Thanks

          Comment


          • #6
            Ok, so the explain show that there is no index used by the request for the table statistics, if it's possible juste create a new index on zip column or modify your primary key to include zip column.

            Comment


            • #7
              After creating two seperate indexes on zipfrom and zipto the explain plan returns:

              1 SIMPLE sta ALL NULL NULL NULL NULL 13074
              1 SIMPLE zip ALL zipfrom,zipto NULL NULL NULL 38065 Using where

              But still the performance is hopeless

              Thanks

              Comment


              • #8
                Humm, the 2 indexes you created are on the wrong table, you need to create 1 index on "stat" table.
                And the 2 new indexes (zipfrom,zipto) are not very good, you should drop them, and let the optimiser use the PRIMARY key.

                Comment

                Working...
                X