Table structure
Id primary key
The name varchar 50

SELECT * FROM ` ABC ` WHERE name like '% 123%' limit 0, 10

SELECT count (*) FROM ` ABC ` WHERE the name like '% 123%'

A query is 10, two is the total number of statistics

The database engine is currently using MyISAM

CodePudding user response:

Not a good way to, would like a full table scan

CodePudding user response:

With the index and use is equal to the judgment will be soon

CodePudding user response:

%, before and after you this kind of like what all useless, is slow, you can see from your data, break up your data, don't use the like query

CodePudding user response:

First check do not use *, another indexed properly,

CodePudding user response:

I this data, but only 300 w, the COUNT directly, without any query conditions, also slow, and direct the where id> 0, also slow

CodePudding user response:

A full table scan, name add index, like the previous percent confirm ok take out, this can be used to the index

CodePudding user response:

Like in front of the variable %, don't walk index, so slow,

CodePudding user response:

? DDL index

http://www.verejava.com/? Id=1717413695356

CodePudding user response:

If the query SQL fixed, can be dealt with by means of pretreatment
This scenario, it is suggested that using full-text index to query, can't check the database directly

CodePudding user response:

Have a try, not sure ok,
SELECT * FROM ` ABC ` WHERE NAME REGEXP '123' LIMIT 0, 10;
SELECT COUNT (*) FROM ` ABC ` WHERE the NAME REGEXP '123'.

CodePudding user response:

Upstairs said
The index this is must be
% % like this certainly not, no matter how to write slowly, if it is to like, the best is for index field

SELECT * FROM ` ABC ` WHERE name like '123%' limit 0, 10

Count (*) statistics to count (0), if the name no index, ten million data is expected to be a few seconds, build a index, speed should be within a few seconds,
SELECT count (0) FROM ` ABC ` WHERE the name like '123%'

CodePudding user response:

The original poster hello, it is recommended that the following
1: query many fields, such as name, plus index
2: query field, can write the field out, don't use select *
The single table of 000000, even against ali cloud RDB (SSD, highly optimized) is large, suggests that the building Lord consider table operations

CodePudding user response:

Increase the full line: field full index

Set the mysql file:
Port=3306
Innodb_ft_min_token_size=1
Ft_min_word_len=1

Query methods:
MATCH (` field name `) AGAINST () 'value'