# Need Faster query results

**URL:** <https://forums.percona.com/t/need-faster-query-results/1213>\
**Category:** Other MySQL® Questions\
**Created:** [July 17, 2009, 1:27pm UTC](https://forums.percona.com/t/need-faster-query-results/1213 "2009-07-17T13:27:28Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![greenoctopus](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greenoctopus](https://forums.percona.com/u/greenoctopus)\
**Post date:** [July 17, 2009, 1:27pm UTC](https://forums.percona.com/t/need-faster-query-results/1213/1 "2009-07-17T13:27:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [July 17, 2009, 2:38pm UTC](https://forums.percona.com/t/need-faster-query-results/1213/2 "2009-07-17T14:38:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![greenoctopus](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greenoctopus](https://forums.percona.com/u/greenoctopus)\
**Post date:** [July 17, 2009, 2:49pm UTC](https://forums.percona.com/t/need-faster-query-results/1213/3 "2009-07-17T14:49:26Z")

</div>

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

Octo

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [July 17, 2009, 3:13pm UTC](https://forums.percona.com/t/need-faster-query-results/1213/4 "2009-07-17T15:13:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![greenoctopus](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greenoctopus](https://forums.percona.com/u/greenoctopus)\
**Post date:** [July 17, 2009, 3:21pm UTC](https://forums.percona.com/t/need-faster-query-results/1213/5 "2009-07-17T15:21:49Z")

</div>

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’

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [July 17, 2009, 3:53pm UTC](https://forums.percona.com/t/need-faster-query-results/1213/6 "2009-07-17T15:53:45Z")

</div>

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.