Announcement

Announcement Module
Collapse
No announcement yet.

Need Faster query results

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

  • Need Faster query results

    We have 3 large tables. All holding around 4 million records each. We have made them all into MEMORY tables.

    DROP TABLE IF EXISTS `TWI_READ`.`xxx_IND95`;
    CREATE TABLE `TWI_READ`.`xxx_IND95` (
    `id` int(11) unsigned NOT NULL,
    `ext` varchar(20) NOT NULL default '',
    PRIMARY KEY (`id`),
    KEY `IND95` (`ext`,`id`)
    ) ENGINE=MEMORY DEFAULT CHARSET=latin1;

    DROP TABLE IF EXISTS `TWI_READ`.`xxx_IND46`;
    CREATE TABLE `TWI_READ`.`xxx_IND46` (
    `id` int(11) unsigned NOT NULL,
    `rank` mediumint(9) unsigned default NULL,
    PRIMARY KEY (`id`),
    KEY `IND46` (`rank`,`id`)
    ) ENGINE=MEMORY DEFAULT CHARSET=latin1;

    DROP TABLE IF EXISTS `TWI_READ`.`xxx_IND6`;
    CREATE TABLE `TWI_READ`.`xxx_IND6` (
    `id` int(11) unsigned NOT NULL,
    `end_datetime` datetime default NULL,
    PRIMARY KEY (`id`),
    KEY `IND6` (`end_datetime`,`id`)
    ) ENGINE=MEMORY DEFAULT CHARSET=latin1;

    When we run this simple query it takes 4 seconds to run. We were assuming it would run in less than 1 seconds. Do you know why it would take so long?

    SELECT IND6.ID from xxx_IND6 IND6, xxx_IND46 IND46 , xxx_IND95 IND95 Where 1=1 and IND6.id = IND46.id and IND6.id = IND95.id and IND95.EXT NOT IN ('', 'com', 'org', 'cc', 'info', 'name', 'biz', 'tv', 'us', 'ws', 'mobi', 'de', 'am', 'fm', 'eu', 'ca', 'me', 'asia', 'in', 'fr', 'ch', 'es', 'uk', 'la', 'cn', 'nl', 'no', 'au', 'pro', 'kr', 'jp', 'it', 'ru') Order by IND46.RANK Desc, IND46.ID Desc Limit 100, 50

    Octo

  • #2
    What does the output of EXPLAIN look like?

    I might be wrong, but I believe MEMORY tables use hash indexing by default, which might not be optimal for sorts and large joins.

    Comment


    • #3
      If we change it to BTREE INDEXING would that help tremendously?
      or would there be an even better alternative?

      Octo

      Comment


      • #4
        The hash thing is just a hunch, I almost never use hash indexes because I usually need range scanning, so I don't have a good feel for what the performance difference might be.

        Really, you should post the results of EXPLAIN. It will tell you straight away if something bad like the creation of on-disk temporary tables is happening.

        Comment


        • #5
          Here you go:

          1, 'SIMPLE', 'IND46', 'index', 'PRIMARY', 'IND46', '8', '', 4357035, ''
          1, 'SIMPLE', 'IND6', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'IND46.domain_id', 1, ''
          1, 'SIMPLE', 'IND95', 'eq_ref', 'PRIMARY,IND95', 'PRIMARY', '4', 'IND6.domain_id', 1, 'Using where'

          Comment


          • #6
            Try these out and see what you get:

            EXPLAIN SELECT id FROM xxx_IND95 WHERE EXT NOT IN (...your list...);

            EXPLAIN SELECT IND6.ID FROM xxx_IND6 IND6, xxx_IND95 IND95 WHERE IND6.ID = IND95.id;

            One other thing you should look at is if the EXT column is too large. Memory tables have fixed width rows, so varchar(20) gets converted to char(20). If you don't really need 20 characters, make it smaller and you should see a corresponding performance improvement. Right now, your index entries are probably around 28 bytes, so you need to read 116 MB to scan that index.

            Comment

            Working...
            X