Re: How to enable index fast in MyISAM table
Posted by:
Gejun Shen
Date: March 24, 2014 12:16AM
Rick James Wrote:
-------------------------------------------------------
> > explain showes it use correct process_sid index.
>
> > KEY `process_sid` (`process_sid`,`action`),
> > select xxx from servicetable where
> process_sid="xxx" and action in (xxx, ...) order
> by session_seq
>
> That index is optimal for that query.
Yes. I think so.
> > explain partitions select count(*) from
> servicetable where timestamp>"2014-03-21 00:00:00"
> and timestamp<"2014-03-22 00:00:00" and
> date="2014-03-21";
>
> That failed to say "Using index". File a bug at
> bugs.mysql.com
I think it not a bug. In this case, date is used for partition pruning, and timestamp will be used as the search index. But when date=xxx" is evaluated, the date information need be read from data file and it can't use index only.
Compared to the statement with no partition pruning(no "date=xxx"), the query needn't go to data file for information. However, it need go to every partition for result. The fortunate thing is most partitions will return nothing except one partition and the merging will still be efficient.
Please point me out if I am wrong.
> I do not know if it matters that `date` and
> `timestamp` are NULLable, but it might.
I will try to test it and share the result.
PS: It's said sharding will added to MySQL. Is it true? Do you have some recommendation on sharding libraries for MySQL?
Subject
Views
Written By
Posted
3847
March 04, 2014 07:26PM
2070
March 08, 2014 12:42PM
2381
March 13, 2014 10:49PM
1843
March 14, 2014 06:32PM
1791
March 17, 2014 01:38AM
1726
March 20, 2014 06:33PM
1946
March 22, 2014 07:23PM
1738
March 23, 2014 02:36PM
Re: How to enable index fast in MyISAM table
1715
March 24, 2014 12:16AM
1760
March 24, 2014 10:49PM
1804
March 25, 2014 08:39PM
1985
March 26, 2014 11:55AM
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.