The size of mysql20W data is 1 GB and the like is very slow. how can this problem be solved?

The size of mysql20W data is 1 GB and the like is very slow. how can this problem be solved. $ SQL = SELECT * 'FROM 'text' where 'title' LIKE' % bbd % 'limit0, 10 & nbsp; $ SQL = mysql_query ($ SQL ); in this case, if we want to calculate the size of 20 W li mysql data in 1 GB within 1 second, we can use like to solve this problem.
$ SQL = SELECT * 'FROM 'text' where 'title' LIKE' % bbd % 'limit
$ SQL = mysql_query ($ SQL); in this way, within 1 second


However, if you want to obtain the total number of like statements, it will be very slow for 10 seconds +... Find a solution ..
$ Sqll = SELECT 'id' FROM 'title' where 'title' LIKE '% bbd %'
$ Sqll = mysql_query ($ SQL );
$ Num = mysql_num_rows ($ sqll); in this case, it takes 10 + seconds.

------ Solution --------------------
You can use sphinx for search.
------ Solution --------------------
No way. You are scanning the entire table. Indexing failed!
------ Solution --------------------
$ Sqll = SELECT count (*) FROM 'title' where 'title' LIKE '% bbd %'
$ Sqll = mysql_query ($ SQL );
List ($ num) = mysql_fetch_row ($ sqll );

It may be faster



------ Solution --------------------
It is obvious that full-text indexes are used. simple FULLTEXT is used. complicated third-party storage engines, such as sphinx, and x are used.
------ Solution --------------------
Discussion

Reference:

It is obvious that full-text indexes are used. simple FULLTEXT is used. complicated third-party storage engines, such as sphinx, and x are used.

How FULLTEXT?

------ Solution --------------------
Select * from tableA from content like "xxx %"
Mysql seems to have only one like method that can use indexes.
Even if the full-text index is complete, there are many restrictions. you have to analyze it based on the actual situation of your database.

Fulltext works like this,
1. FULLTEXT indexes can only be used in MyISAM tables.
2. query by MATCH (col1, col2,...) AGAINST ('Retrieved string ')

Eg;
1. add a full-text index column to the table
Alter table 'text' add fulltext 'fidx _ title' ('title ')
2. execute the query
SELECT * FROM 'text' where match (title) AGAINST ('ddd ')

In a difficult situation, I won't ..








------ Solution --------------------
Discussion

Reference:

To like, there is no solution...

Fulltext is less than 10 seconds in 1 second +

------ Solution --------------------
Only fulltext queries are available. Mysql is very slow once it reaches a certain size.
------ Solution --------------------
Copy and paste the post. In fact, you can use your own GG, mainly because of whether you want transactions...

Innodb
InnoDB provides MySQL with a transaction-safe (ACID compliant) table with transaction (commit), rollback, and crash recovery capabilities. InnoDB provides locking on row level and non-locking read in SELECTs ). These features improve the performance of multi-user concurrent operations. In the InnoDB table, no need to expand the lock escalation, because the row level locks of InnoDB is suitable for a very small space. InnoDB is the first MySQL table engine to provide foreign key constraints (foreign key constraints.

InnoDB is designed to handle large-capacity database systems. its CPU utilization is incomparable to other disk-based relational database engines. Technically, InnoDB is a complete database system on the MySQL background. InnoDB establishes a dedicated buffer pool in the primary memory for high-speed data buffering and indexing. InnoDB stores data and indexes in tablespaces and may contain multiple files, which is different from others. for example, in MyISAM, tables are stored in separate files. The size of the InnoDB table is limited by the file size of the operating system, generally 2 GB.
All InnoDB tables are stored in the same data file ibdata1 (multiple files or independent tablespace files), which is relatively difficult to back up, the free solution can be copying data files, backing up binlogs, or using mysqldump.


MyISAM
MyISAM is the default storage engine of MySQL.

Each MyISAM table is stored in three files. Frm file storage table definition. The data file is MYD (MYData ). The index file is an extension of MYI (MYIndex.

Because MyISAM is relatively simple, it is much more efficient than InnoDB .. it is a good choice for small applications to use MyISAM.

MyISAM tables are saved as files. using MyISAM storage in cross-platform data transfer saves a lot of trouble.

The following are some differences between details and specific implementations:

1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. when deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. the load table from master operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.
------ Solution --------------------
If you want to use innodb + full-text indexing, you can directly change the sphinx to achieve master-slave read/write splitting.