Shulou(Shulou.com)05/31 Report--

This article mainly explains "the importance and example use of mysql index". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn the importance of mysql index and the use of examples!

First, the importance of the index

The index is used to quickly find rows that have a specific value in a column. Without using an index, MySQL must start with the first record and then read the entire table until it finds the relevant row. The bigger the watch, the more time it takes. If the query column in the table has an index, MySQL can quickly get to a location to find the middle of the data file, and there is no need to look at all the data. Note that sequential reads are much faster if you need to access most of the rows, because we avoid disk search at this time.

If you use Xinhua Dictionary to look up the Chinese character "Zhang" instead of using catalogs, you may have to find the last page from the first page of Xinhua Dictionary, which may take two hours. The thicker the dictionary, the more time you spend. Now you use the catalog to find the Chinese character "Zhang". The first letter of Zhang is a Chinese character that begins with zrecovery. starting from more than 900 pages, with this clue, it may only take you one minute to find a Chinese character, which shows the importance of the index. But is it better to build as many indexes as possible? of course not. If the catalogue of a book is divided into several levels, I think you will be dizzy, too.

Second, preparatory work

/ / prepare two test tables

> CREATE TABLE `test_ t` (

-> `id` int (11) NOT NULL auto_increment

-> `num` int (11) NOT NULL default 0

-> `dnum` varchar (30) NOT NULL default 0

-> PRIMARY KEY (`id`)

->) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

Query OK, 0 rows affected (0.05 sec)

Mysql > CREATE TABLE `test_ test` (

-> `id` int (11) NOT NULL auto_increment

-> `num` int (11) NOT NULL default 0

-> PRIMARY KEY (`id`)

->) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

Query OK, 0 rows affected (0.05 sec)

/ / create a stored procedure to facilitate data insertion

Mysql > delimiter |

Mysql > create procedure i_test (pa int (11), tab varchar (30))

-> begin

Declare max_num int (11) default 100000

-> declare i int default 0

-> declare rand_num int

-> declare double_num char

->

-> if tab! = test_test then

-> select count (id) into max_num from test_t

-> while I

If max_num

Select cast (rand () * 100as unsigned) into rand_num

-> select concat (rand_num,rand_num) into double_num

-> insert into test_t (num,d_num) values (rand_num,double_num)

-> end if

-> set I = I + 1

-> end while

-> else

-> select count (id) into max_num from test_test

-> while I

If max_num

Select cast (rand () * 100as unsigned) into rand_num

-> insert into test_test (num) values (rand_num)

-> end

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.