MySQL Query Tuning

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

CREATE TABLE `TABLE` (
  `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 '0.0.0.0',
  `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?
BuickFreakAsked:
Who is Participating?
 
lokusCommented:
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
0
 
crimson117Commented:
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.
0
 
crimson117Commented:
Also, why do you use char(1)?  If those fields are basically yes/no fields, try using ENUM column types.
0
 
BuickFreakAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.