MySQL 20W data 1G size with like odd slow, seek solution

MySQL 20W data 1G size with like odd slow, seek solution.
$sql =select * ' from ' the text ' where ' title ' Like '%bbd% ' limit 0,10
$sql =mysql_query ($sql); In less than 1 seconds.


But to ask like to get the total number is very slow 10 seconds + ... To find a solution.
$SQLL =select ' id ' from ' title ' where ' title ' Like '%bbd% '
$SQLL =mysql_query ($sql);
$num = mysql_num_rows ($SQLL); It's in the second.

------Solution--------------------
Can be retrieved using Sphinx
------Solution--------------------
No way. You are scanning the whole table. Unable to walk index!
------Solution--------------------
$SQLL = SELECT count (*) from ' title ' where ' title ' Like '%bbd% '
$SQLL = mysql_query ($sql);
List ($num) = Mysql_fetch_row ($SQLL);

It could be a little faster.



------Solution--------------------
It is obvious to use full-text index, simple point of direct Fulltext, complex with third-party storage engine, such as Sphinx, there is an X what, watercress in use.
------Solution--------------------
discuss

Reference:

It is obvious to use full-text index, simple point of direct Fulltext, complex with third-party storage engine, such as Sphinx, there is an X what, watercress in use.

How fulltext?

------Solution--------------------
SELECT * from TableA to content like "xxx%"
MySQL seems to have only such a like way to use the index.
Even if the full-text index, there are many restrictions, their own database according to the actual situation of the analysis

Fulltext probably works like this,
1. Fulltext index can only be used for MyISAM table
2. Using match (Col1,col2,...) Against (' retrieved string ') for querying

eg
1. Add a full-text index column to the table
ALTER TABLE ' text ' ADD fulltext ' Fidx_title ' (' title ')
2. Execute Query
SELECT * from ' text ' WHERE MATCH (title) against (' DDD ')

In the difficult I would not have.








------Solution--------------------
discuss

Reference:

To like basically no solution ah ...

Fulltext after 1 seconds without 10 seconds +

------Solution--------------------
Only the fulltext query. MySQL is incredibly slow when it's a certain size.
------Solution--------------------
Copy sticky, in fact, can be self-GG, mainly business you want to ...

InnoDB
InnoDB provides MySQL with transaction security with transactional (commit), rollback (rollback), and crash-repair capabilities (crash recovery capabilities) (Transaction-safe (ACID compliant )) Type table. The InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) that is consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. There is no need to widen the lock (lock escalation) in the InnoDB table because the InnoDB column lock (Row level locks) is suitable for very small space. InnoDB is the first table engine on MySQL to provide a foreign key constraint (FOREIGN key constraints).

InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL, InnoDB in the main memory to establish its dedicated buffer pool for caching data and indexes. InnoDB the data and index in the table space, may contain multiple files, which is different from other, for example, in MyISAM, the table is stored in a separate file. The size of the InnoDB table is limited only by the size of the operating system file, typically 2 GB.
InnoDB All tables are stored in the same data file Ibdata1 (or possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, the free scheme can be copy data files, backup Binlog, or mysqldump.


MyISAM
MyISAM is the MySQL default storage engine.

Each of the MyISAM tables is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). The index file is an myi (myindex) extension.

Because MyISAM is relatively simple, it is better than innodb in efficiency. Using MyISAM for small applications is a good choice.

MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle

The following are some of the details and the specific implementation differences:

1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.
------Solution--------------------
Want to innodb+ full-text index, direct change sphinx, master and slave read and write separation.

