large mysql database slow acctions

Question

i have a database like this

it have content of a site i grabbed with a php script and linux cron jobs

after i got all pages of the sites it goes to work slowly

and server load is:

now i cant run a small query like this

SELECT * FROM `content` WHERE `html` LIKE '%%simple%%'

i think 3gb is not to much for mysql! the server have dual 5620 cpu with 32 g of ram with this hardware i think ed it can handle up to 2tb of db!!

UPDATE 1 : my content table is like this

i have one index and its the id but a query like this need a lot of time to run too

 <?php echo mysql_num_rows(mysql_query("SELECT * FROM pages where `update_date`!='0000-00-00 00:00:00' and `type`='page';")); ?>

you mean i just change the html field to full text!?

Answer 1

Using LIKE as you are with wildcards on both sides does not allow MySQL to utilize an index (if the field is indexed) so 3GB of database to slog through would actually take quite a while. I would recommend removing the left hand wildcards and (potentially) taking a look at MATCH AGAINST using a FULLTEXT index.

For more: http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

It is worth noting that in MySQL PRIOR to version 5.6 you will need to convert your table to MyISAM to utilize the FULLTEXT engine. In 5.6 and up you can use them in InnoDB as well as MyISAM. If for some reason you can't upgrade or use 5.6+, then you could always setup a MyISAM table with only the information you need to have stored for FULLTEXT purposes. Then setup triggers to duplicate/remove information from the MyISAM table as it gets deleted from the InnoDB. This may not work within your project goals, but it is one solution.

Answer 2

Every time that query runs you're searching through 3 billion characters for a string match.

You need to use an index.

I also think you're using the wrong query predicate and should use a full-text-search which is designed for this sort of thing. You also want to index the full-text-search.