MySQL Forums
Forum List  »  MyISAM

Re: How to enable index fast in MyISAM table
Posted by: Rick James
Date: March 14, 2014 06:32PM

> select xxxx from servicetable where timestamp>="xxx" and timestamp<"xxx" ...
> select xxx from servicetable where process_id="xxx" ...

Neither of these can do "partition pruning", so they check all the partitions.
(But that's not a serious issue.)

If you were to conver to InnoDB, seq should be the PRIMARY KEY, not a regular index.

> `ipnum` int(10) unsigned DEFAULT '0',

Is that an IP address? If so, it can't hold IPv6.

> `filesize` int(10) DEFAULT '0',

Files are not limited to 2 billion bytes. INT UNSIGNED would give you 4B; BIGINT would be necessary for bigger.

Shrinking the datatypes wherever possible will decrease I/O, which will speed up queries.

> `servicetype` int(4) unsigned DEFAULT '9',
> `isarchive` int(4) unsigned DEFAULT '0',

That takes 4 bytes because it is INT (not because of "(4)", which means nothing. TINYINT might be more appropriate (1 bytes).

You have several VARCHAR(32) for some kinds of ids. What kind of data is in them? GUIDs? Other?

If those ids have hex in them, then this should be overridden with ascii:
> DEFAULT CHARSET=utf8
Or you should pack them into BINARY(16).

Can any of the bulky fields be reasonably 'normalized'? That might turn 32 bytes into 3 (MEDIUMINT UNSIGNED) or 4 (INT UNSIGNED).

> How many connections can mysqld handle without obvious performance downgrade?

Number of connections (even thousands) is not an issue.
Number of queries running at the same time is an issue.

> select xxxx from servicetable where timestamp>="xxx" and timestamp<"xxx" ...

could be sped up a little by adding
AND date >= ... AND date < ...
in order to get pruning.

> where process_id="xxx" ...
is stuck with hitting all the partitions. Even if that is a typo for process_sid.

> `original_files` mediumtext,
> `filelist` mediumtext,
> `memo` mediumtext,

Do you often fetch these fields? Are they usually bulky? Consider moving them to a 'parallel' table. This would decrease I/O somewhat.

I see several fields, even some indexed fields, being NULLable. Was this a mistake?

In some later version, you can effectively get PARTITION BY RANGE(timestamp); this might be a better way to go.

I cannot predict whether InnoDB would be faster or slower. Probably slower because of being bulkier. But some of that is softened by the differences in index structures and caching. InnoDB would probably shine for the first query if you had
PRIMARY KEY(timestamp, seq), INDEX(seq).
That way it could take advantage of 'clustering' of the PK.

You do have new enough version to set key_buffer_size bigger than 4G. Suggest 10G for your 48GB RAM.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to enable index fast in MyISAM table
1724
March 14, 2014 06:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.