Hi there. I have 2 tables client and sites. Client is basically a company and if they have different sites or branches these will be listed in sites. Sites can have their own account numbers different from the main company. So each table has a column called motekacc. If I perform a search with the query below it takes a long time. is there something that I can do to speed it up? SELECT DISTINCT client.id as companyid, client.company_name, client.address, client.postcode, client.motekacc , sites.motekacc FROM sites, client WHERE ( ( client.motekacc LIKE "CON009%") OR ( sites.motekacc LIKE "CON009%" AND sites.client=client.id) ) AND client.type>-1 GROUP BY client.id ORDER BY client.company_name ASC, client.id ASC Code (markup): Thanks,
The LIKE parts are most likely making this really slow, as they will not use indexes and require the database to perform a lot. You can run EXPLAIN your_query and get some better info on how it is executed. Unless you can get rid of the LIKE's there probably isn't much you can do to speed it up.
Thanks for the help. I ran explain and this it what came back id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE sites ALL NULL NULL NULL NULL 1958 Using temporary; Using filesort 1 SIMPLE client ALL PRIMARY NULL NULL NULL 67649 Using where Code (markup):
Hi, type is MySQL reserved word and should not be used as column name. Also your join has partial match in where condition on foreign keys (assuming sites.client=client.id, applied in OR condition). Group by clause applying such way is not correct. SELECT DISTINCT client.id as companyid, client.company_name, client.address, client.postcode, client.motekacc , sites.motekacc FROM sites, client WHERE (sites.client=client.id) AND (client.`type`>-1) AND (client.motekacc LIKE "CON009%" OR sites.motekacc LIKE "CON009%") ORDER BY client.company_name ASC,companyid ASC; Code (markup): Please provide create table script for both sites and client tables, if this still not working right or slow. Regards
Thanks for the hints. Here are the create table scripts. -- phpMyAdmin SQL Dump -- version 3.1.2deb1ubuntu0.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Mar 17, 2010 at 02:10 PM -- Server version: 5.0.75 -- PHP Version: 5.2.6-3ubuntu4.1 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE TABLE IF NOT EXISTS `client` ( `id` int(11) NOT NULL auto_increment, `company_name` varchar(255) NOT NULL default '', `account_number` varchar(20) NOT NULL default '', `postcode` varchar(10) NOT NULL default '', `country` varchar(100) NOT NULL default '', `acc_man_c` int(11) NOT NULL default '0', `source` varchar(50) NOT NULL default '', `address` text NOT NULL, `indsector` varchar(200) NOT NULL default '', `web` varchar(200) NOT NULL default '', `employees` varchar(100) NOT NULL default '', `motekacc` varchar(200) NOT NULL default '', `gbacc` varchar(200) NOT NULL default '', `externalacc` varchar(200) NOT NULL default '', `vehicles` varchar(200) NOT NULL default '', `type` int(11) NOT NULL default '0', `x_cdbjoin` varchar(50) NOT NULL, `group` text NOT NULL, `phone` varchar(100) NOT NULL, `fax` varchar(100) NOT NULL, `cleanphone` varchar(100) NOT NULL, `cleanfax` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `acc_man_r` int(11) NOT NULL, `acc_man_t` int(11) NOT NULL, `delivery_instructions` text NOT NULL, `special_price` text NOT NULL, `terms_conditions` text NOT NULL, `special_instructions_note` text NOT NULL, `freq` int(11) NOT NULL, `tps` int(1) NOT NULL default '0', `noemail` int(1) NOT NULL default '0', `nomail` int(1) NOT NULL default '0', `acc_man_s` int(11) NOT NULL, `org_acc_man` varchar(100) NOT NULL, `updated` date NOT NULL, `account_locked` int(11) NOT NULL default '0', `cash_only` int(11) NOT NULL default '0', `deleted_by` int(11) NOT NULL default '0', `deleted_date` date NOT NULL default '0000-00-00', `directdebit` int(11) NOT NULL, `created_by` int(11) NOT NULL, `created_on` date NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `address` (`address`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=79442 ; CREATE TABLE IF NOT EXISTS `sites` ( `id` int(11) NOT NULL auto_increment, `name` varchar(200) NOT NULL default '', `client` int(11) NOT NULL default '0', `postcode` varchar(100) NOT NULL default '', `country` varchar(100) NOT NULL default '', `phone` varchar(100) NOT NULL, `fax` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `web` varchar(100) NOT NULL, `employees` int(11) NOT NULL, `vehicles` int(11) NOT NULL, `address` text NOT NULL, `group` int(11) NOT NULL, `freq` int(11) NOT NULL, `cleanphone` varchar(100) NOT NULL, `cleanfax` varchar(100) NOT NULL, `deleted` int(11) NOT NULL, `acc_man_c` int(11) NOT NULL, `acc_man_s` int(11) NOT NULL, `acc_man_r` int(11) NOT NULL, `acc_man_t` int(11) NOT NULL, `motekacc` varchar(200) NOT NULL, `gbacc` varchar(200) NOT NULL, `externalacc` varchar(200) NOT NULL, `created_by` int(11) NOT NULL, `created_on` date NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `address` (`address`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1963 ; Code (markup): Your help is greatly appreciated.
OK I have managed to fix the problem. I have rewritten the query and now I am using UNION instead of having an OR and it seems to work like a dream.