0

I am running around 50,000 queries on a MyISAM DB however the longer the query runs the slower it becomes. For example, at the beginning 20 rows can be done in a second and by the end it is doing 5 rows a second. Is this happening because MyISAM locks the tables after each insert or is it some other reason?

I think I have to use MyISAM because I am using match()against() with Fulltext indexes, which as far as I know only MyISAM supports. Is it possible to use InnoDB or some other DB type with this functionality or is there a way to speed up the queries as the execution time for the current query is about 45mins.

I anticipate a lot of answers about my query structure, I am collating three tables with different columns into one table and trying to match up the 3 different rows together to form a 'supertable' as a result I need to do a lot of match()against(), preg_match, selects, and looping in order to match them up with accuracy. In order to optimise my loops I am using PDO with prepared statements but this has only brought down the query time to the time I mentioned above, using mysql_connect and the standard functions it was closer to an hour.

Remember the query isn't slow to begin with, only after it's done about 10,000 queries does it slow down noticeably and as it does more it becomes slower and slower until it reaches an unreasonable level for any server company to accept, can anyone give me a solution?

Ben Jackson
  • 1,427
  • 3
  • 14
  • 24
  • 2
    show us the query, and the db schema. And yes you have to use MyISAM for full text indexing. there's a few settings you could tweak around buffer and temporary table sizes. –  Jul 05 '12 at 20:38
  • Dagon, that's no longer true as of MySQL 5.5 -- full text and geospatial indexing are now supported on InnoDB as well. –  Jul 05 '12 at 22:11

2 Answers2

0

My recommendation is try to update your MySQL,

If you have a lot of writting MySQL -V 5.6+ which allow you to use full text index in InnoDB Tables, beside that Innodb allow you to make rows lock so that is a HUGE thing.

You can also try to repair index with ANALIZE that could help a little bit the performance

jcho360
  • 3,724
  • 1
  • 15
  • 24
0

MySQL Full text search can't efficiently handle big data by design. Switching from MyISAM to InnoDB is good idea most of the time as it will help with non-FT queries performance and concurrency and will keep you data safe as InnoDB is supporting transaction, but will not boost your FT queries speed much (according to Percona's benchmarks http://bit.ly/M6DMsj ).

I would suggest to move Full-Text queries out of MySQL. Any external search engine like Solr or Sphinx will be very helpful in this case.

I'm not an expert with Solr, but in case you decided to use Sphinx you could use http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/ as a guide for initial configuration.

Hope this helps.

vfedorkov
  • 847
  • 5
  • 8