Solved

MySQL Query Tuning

Posted on 2004-10-23
221 Views
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

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?
0
Question by:BuickFreak
    4 Comments
     
    LVL 5

    Expert Comment

    by:crimson117
    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
     
    LVL 5

    Expert Comment

    by:crimson117
    Also, why do you use char(1)?  If those fields are basically yes/no fields, try using ENUM column types.
    0
     
    LVL 8

    Accepted Solution

    by:
    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
     

    Author Comment

    by:BuickFreak
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now