1

I have 1 MyISAM table with 620,000 rows. Im running XAMPP on a Dual Core Server with 2GB RAM. Apache is installed as a Windows Service, MySQL is controlled from the XAMPP control panel.

The query below is taking 30+ seconds to run.

select  `id`,`product_name`,`search_price`,`field1`,`field2`,
       `field3`,`field4`
    from  `all`
    where  MATCH (`product_name`) AGAINST ('searchterm')
      AND  `search_price` BETWEEN 0 AND 1000
    ORDER BY  `search_price` DESC
    LIMIT  0, 30

I have a FULLTEXT index on product_name, a BTREE on search_price, auto increment on id

If I explain the above query the results are:

id select_type   table type     possible_keys                       key                   key_len ref rows Extra
1  SIMPLE        all   fulltext search_price,FULLTEXT_product_name  FULLTEXT_product_name 0 NULL 1 Using where; Using filesort

How can I speed up this query? Should it be taking this long on a table of 620,000 rows?

Ive just noticed that this only happens when the database has not been queried for a while, so im guessing this is to do with the cache, the first query is taking 30+ seconds, then if I try a second time the query takes 1 second

Rick James
  • 135,179
  • 13
  • 127
  • 222
asdf1234
  • 127
  • 4
  • 10

2 Answers2

2

MySQL will do the fulltext search first, then look up the rest of the info, filter on price, sort on price, and finally deliver 30. There is essentially no way to shorten that process.

Yes, caching is likely to be the explanation for 30 seconds becoming 1 second.

Switching to InnoDB (which now has FULLTEXT) may provide some benefits.

If running entirely MyISAM, do you have key_buffer_size set to about 20% of available RAM? If you were much lower (or higher) than this, that could cause performance problems.

If running entirely InnoDB, set innodb_buffer_pool_size to about 70% of available RAM.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I cant find `key_buffer_size` in my.ini but it is set as 16777216, I can see key_buffer under `[mysqld] key_buffer = 16M` `[isamchk] = 20M` and `[myisamchk] = 20M`. I will try using Innodb with `innodb_buffer_pool_size = 400MB` – asdf1234 Mar 10 '16 at 07:05
  • I tried using Innodb with the buffer pool at 400MB, I also tried setting the `key_buffer_size = 100MB` and the queries are still 10-15+ seconds unless they are cached. Is there anything else I can try to speed this query up? Should I be using a different database setup instead of MySQL? Would upgrading my server help? – asdf1234 Mar 10 '16 at 09:11
  • Sagacious has suggestions on how to change the setup. I have no further suggestions. – Rick James Mar 11 '16 at 01:43
  • Im running this setup on a Cloud server... could this be why MySQL is performing slowly? Ive run the same setup on my personal computer and the queries are much faster. Would switching to a more powerful dedicated server help? – asdf1234 Mar 16 '16 at 03:48
  • Unlikely. Let's check another thing -- `SHOW VARIABLES LIKE 'key_buffer_size';` on both machines. – Rick James Mar 16 '16 at 05:18
  • 100MB on the cloud server, 16MB on my computer – asdf1234 Mar 16 '16 at 05:37
  • just tried the same query on both machines, 12 seconds on my computer and then 1 minute 11 seconds on the cloud server – asdf1234 Mar 16 '16 at 06:40
  • Do you have SSDs, but the cloud has spinning drives? – Rick James Mar 16 '16 at 19:13
1

MySQL's capability of dealing with FULLTEXT is somewhat limited when th size of the table goes above 300,000. And it will peform even worse if you use really common words as search keywords like (in,the,of, etc commonly marked as stop words). I recommend using Sphinx Full Text Search/ Apache Lucene

Stackoverflow links:

Comparison of the two

More Comparison

Community
  • 1
  • 1
SagaciousLearner
  • 88
  • 1
  • 5