Need Faster query results

MySQL & MariaDB Other MySQL® Questions
1

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.

3

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

Octo

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.

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’

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.