Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


MySQL Query Tuning

Posted on 2004-10-23
Medium Priority
Last Modified: 2008-02-01
I'm trying to figure out a slow query. This query is being executed on a DB with about 500K rows.  Here is the query:

SELECT * FROM TABLE where MATCH (webdesc) AGAINST ('buick' IN BOOLEAN MODE) AND approval=1 and vermail=1 and approved <= NOW() AND gtype='p' AND thumb!='1' order by approved desc, rate desc, idnum desc

This is what I get from explain:

table  type  possible_keys  key  key_len  ref  rows  Extra  
TABLE fulltext approved,approval,gtype,webdesc_2 webdesc_2 0   1 Using where; Using filesort

Since the query is returning several thousand rows at times, using filesort is going to cause some speed problems.

Here's the structure

  `webname` varchar(40) NOT NULL default '0',
  `webemail` varchar(40) NOT NULL default '0',
  `weburl` varchar(125) NOT NULL default '0',
  `webcate` varchar(100) NOT NULL default '0',
  `webpics` int(3) NOT NULL default '0',
  `webdesc` varchar(200) NOT NULL default '0',
  `approval` char(2) NOT NULL default '0',
  `idnum` int(15) NOT NULL auto_increment,
  `webip` varchar(15) NOT NULL default '',
  `uniqueid` varchar(8) NOT NULL default '00000000',
  `vermail` char(1) NOT NULL default '1',
  `rate` smallint(2) NOT NULL default '5',
  `aff` tinyint(1) NOT NULL default '0',
  `fsize` int(7) NOT NULL default '0',
  `thumb` char(1) NOT NULL default '0',
  `submitted` date default NULL,
  `approved` date default NULL,
  `gtype` char(1) NOT NULL default '',
  `tfile` decimal(4,2) NOT NULL default '00.00',
  PRIMARY KEY  (`idnum`),
  KEY `approved` (`approved`),
  KEY `submitted` (`submitted`),
  KEY `approval` (`approval`),
  KEY `webdesc` (`webdesc`),
  KEY `gtype` (`gtype`),
  KEY `weburl` (`weburl`),
  KEY `webcate` (`webcate`),
  KEY `idnum` (`idnum`),
  FULLTEXT KEY `webdesc_2` (`webdesc`)

Any ideas on how I can improve the query or the indexing of the DB to gain some speed?
Question by:BuickFreak
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 12396078
Try only putting an index on a column that results in high cardinality.  Meaning, only on columns that have widely differing values.

Try removing the indexes from gtype, approval, vermail, thumb.

Also, keep in mind that ordering a resultset is typically a slower operation, so only order it if you really need to.

Expert Comment

ID: 12418542
Also, why do you use char(1)?  If those fields are basically yes/no fields, try using ENUM column types.

Accepted Solution

lokus earned 1000 total points
ID: 12431109
The query will only use the FULLTEXT index, all other indexes will NOT be usable in this query.

You may consider removing some of the unnecessary indexes though it will not help your seelct statement.

You may want to do a SHOW STATUS before and after the query and compare the Sort_merge_passes value, if it increases then you should increase your sort_buffer_size. See http://dev.mysql.com/doc/mysql/en/Server_status_variables.html

Author Comment

ID: 12441777
That helped a lot. I was under the impression that I could use other indexes at the same time as I was using the fulltext index. The EXPLAIN showed it was using the index but even when I removed the index from the DB, there was no noticable change in speed. It makes sense now. I also increased the sort_buffer_size and my query times were cut in half.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question