Articles of tag under mysql

2018-06-16 16:03:37

icp(index condition pushdown)

icp(acronym of index condition pushdown), which used when index(part of a multicolumn index) can be use to filter out some(enormous maybe) on storage level

  • the output of EXPLAIN cound display some information to indicate user, suck as key_len and key, especially in Extra column which will dislay using index condition
  • I think that only multicolumn index could make use of this method
  • even there exist only one where condition, the icp method would happened, becuase it did
  • need satisfy leftmost prefix
  • For InnoDB tables, ICP is used only for secondary indexes, The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
  • Conditions that refer to subqueries cannot be pushed down.
  • it depends on optimizer and analyse according to statistic to decide where use icp on specific query
Read More »
2018-06-16 16:02:17

index merge


index merge is that mysql sometimes(depends on some) could use seperate index on where condition at storage level, then can scan them simultaneously and them merge the result

  • where clause could affect optimizer whether to use index merge method, especially when intersect on index merge method
  • whether to use the method, I think the cardinatity of a index is very important, the higher the cardinatity the lower the posibility to use the method, because the purpose index merge is to reduce reduncdant rows retrieve, if optimizer think it can not satify this requirement, why use it?
  • the access method merges index scans from a single table; it does not merge scans across multiple tables.
  • type, key, key_len, rows will dispaly different in EXPLAIN when optimizer decide to use this method
  • it's more easier to judge a or opration whether the optimizer decide to use this method than a and opration
Read More »
2018-06-16 16:00:05

a little about query of mysql

There some theory about query on mysql we should know so that we can use it more efficiently.

some important theories

  • avoid reduncdant data access such as appliction retrieving more data than nedd and server level get reduncdant data
  • sometimes a fewer queries would be better than a single query
  • a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. This indicate whether use the index to get data you want is detemined by optimizer and the data on the table
Read More »
2018-06-16 15:52:19

index in mysql

There are some types of index which depends on storage engine. Most used index would be B-Tree indexes, which complement by InnoDB.

B-Tree indexes

  1. InnoDB refers to them by their primary key values
  2. store in order
  3. multicolumn index should satisfy leftmost prefix
  4. the index can be used by ORDER BY,GROUP BY and DISTINCT
Read More »
2017-10-17 09:54:41

linux安装MySQLDb报错mysql_config not found

linux下(我这里是linux mint 18.2)用以下命令安装MySQLDb

pip install MySQL-python


sh: mysql_config: not found
Traceback (most recent call last):
  File "", line 15, in <module>
    metadata, options = get_config()
  File "/home/zhxia/apps/source/MySQL-python-1.2.3/", line 43, in get_config
    libs = mysql_config("libs_r")
  File "/home/zhxia/apps/source/MySQL-python-1.2.3/", line 24, in mysql_config
    raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found
Read More »
2017-10-03 10:39:46



Read More »