Re: How to enable index fast in MyISAM table

[Gejun Shen]

Rick James Wrote:
-------------------------------------------------------
> > I do have thousands of queries during busy
> hour.
>
> So, only a few per second? MyISAM can usually
> handle hundreds, maybe thousands, of _simple_
> queries per second.
>
> MyISAM locks the entire table whenever a write
> happens. InnoDB does not.

In busy hour, there are hundreds of queries per second.
Yes. MyISAM's locking entire table is the most pain point.

I enabled the concurrent insert, however, in some cases it will still block the writing statement and sometimes it will not. Is it normal? The blocking query are like below:

select xxx from servicetable where process_sid="xxx" and action in (xxx, ...) order by session_seq

explain showes it use correct process_sid index.

> > I do see table lock issue in the slow log and
> want to fix that.
>
> That implies finding ways to speed up the slow
> queries.
>
> > I tried adding date to it to prune partition.
> However, it is slower than no pruning. Hence I
> removed the "date=xx" criteria.
>
> Strange. Could you show us the exact SELECTs you
> tried, together with EXPLAIN PARTITIONS SELECT...
> for each.

A simple example would be:

select count(*) from servicetable where timestamp>="2014-03-21 00:00:00" and timestamp<"2014-03-22 00:00:00"

It cost 56.38 seconds

However, with date="2014-03-21" added, it costs several minutes to get finished.

I listed the explain and explain partitions here. Compared to no date criteria, the difference is it use correct partition and it use only "Using where" but not "Using index". My understanding is it need go to data file for "date" information and do the match required in the query, that will definitely much more slower than using index only. Without date criteria, though it has no partition pruning, but it just need go to index file for data, that would be more efficient.
That's my thoughts.

So, My options are upgrade to 5.5 or above to use "timestamp" as partition key or convert it to Innodb which I still worry about the performance and disk usage.

Sorry, below maybe a mess to read.


explain result:
mysql> explain select count(*) from servicetable where timestamp>"2014-03-21 00:00:00" and timestamp<"2014-03-22 00:00:00";
+----+-------------+----------+-------+---------------+-----------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-----------+---------+------+----------+--------------------------+
| 1 | SIMPLE | servicetable | range | timestamp | timestamp | 5 | NULL | 21447798 | Using where; Using index |
+----+-------------+----------+-------+---------------+-----------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)


mysql> explain partitions select count(*) from servicetable where timestamp>"2014-03-21 00:00:00" and timestamp<"2014-03-22
+----+-------------+----------+--------------------------------------------------------------------------------------+-------+---------------+-----------+---------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------------------------------------------------------------------------------------+-------+---------------+-----------+---------+------+----------+--------------------------+
| 1 | SIMPLE | servicetable | p20130930,p20131031,p20131130,p20131231,p20140131,p20140228,p20140331,p20140430,pmax | range | timestamp | timestamp | 5 | NULL | 21448794 | Using where; Using index |
+----+-------------+----------+--------------------------------------------------------------------------------------+-------+---------------+-----------+---------+------+----------+--------------------------+

mysql> 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";
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+----------+-------------+
| 1 | SIMPLE | servicetable | p20140331 | range | timestamp | timestamp | 5 | NULL | 21449654 | Using where |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> explain 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";
+----+-------------+----------+-------+---------------+-----------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-----------+---------+------+----------+-------------+
| 1 | SIMPLE | servicetable | range | timestamp | timestamp | 5 | NULL | 21450071 | Using where |



Edited 2 time(s). Last edit at 03/22/2014 07:30PM by Gejun Shen.