0

I have a tables:

CREATE TABLE `activity` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `daten` int(11) NOT NULL,  `customerid` int(11) NOT NULL,  `activity` varchar(255) NOT NULL,  `reference` int(11) NOT NULL,  `branch` int(11) NOT NULL,  `company` int(11) NOT NULL,  `ad` text NOT NULL,  PRIMARY KEY (`id`),  KEY `customerid` (`customerid`),  KEY `daten` (`daten`),  KEY `activity` (`activity`)) ENGINE=MyISAM AUTO_INCREMENT=153670 DEFAULT CHARSET=latin1
CREATE TABLE `orders` (  `orderid` int(11) NOT NULL AUTO_INCREMENT,  `totalov` int(11) NOT NULL,  `totalprice` int(11) NOT NULL,  PRIMARY KEY (`orderid`)) ENGINE=MyISAM AUTO_INCREMENT=129365 DEFAULT CHARSET=latin1
CREATE TABLE `lead` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `daten` int(11) NOT NULL,  `customerid` int(11) NOT NULL,  `cam` text NOT NULL,  `resp` int(11) NOT NULL,  `followupdate` int(11) NOT NULL,  `status` int(11) NOT NULL,  `det` text NOT NULL,  `source2` text NOT NULL,  `source3` text NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8537 DEFAULT CHARSET=latin1
CREATE TABLE `customer` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` text NOT NULL,  `idnumber` text NOT NULL,  `phone` int(11) NOT NULL,  `phone2` int(11) NOT NULL,  `email` text NOT NULL,  `address` text NOT NULL,  `city` text NOT NULL,  `zip` text NOT NULL,  `lastid` int(11) NOT NULL,  `source` text NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=101661 DEFAULT CHARSET=latin1
CREATE TABLE `cam` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `daten` int(11) NOT NULL,  `name` text NOT NULL,  `link` text NOT NULL,  `resp` int(11) NOT NULL,  `company` int(11) NOT NULL,  `cat` int(11) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=72 DEFAULT CHARSET=latin1

activity with 150,000 records. lead with 8,000 records. customer with 101,000 records. orders 123,000 records

The activity table is like log records for the customer table. I mean that I know because the activity table if the customer have been in the branch, if he submit a lead form or like this. for analyze lead I use this query:

SELECT sum(totalprice) as total from(
SELECT distinct t1.customerid, t2.reference, t3.totalprice
FROM activity t1
INNER JOIN activity t2 on t1.customerid=t2.customerid
INNER JOIN orders t3 ON t2.reference = t3.orderid
INNER JOIN lead t4 on t1.reference=t4.id
INNER JOIN cam t5 on t4.cam=t5.id
WHERE FROM_UNIXTIME(t1.daten, '%m/%y') = '$dates' 
  and t1.activity='lead' 
  and t2.activity='order' 
  and t1.daten < t2.daten $respquery5 
  and t4.resp != 72
)t

I have a page with all the analysis and to this page have a 40 queries like this. This page is very very slow.
Do you have any suggestions on how to improve the query performance?

*The MySQL version is 5.1.63.
*The engine is MyISAM so I don't have a foreign key.
*Are you think that if I will change the engine to InnoDB this will improve the load time?
*And what about the version of MySQL? Will this significantly improve the load time?
*Do you think that the activity being a text field (varchar(255)) like "orders" and not an int field is slowing down the page?
*I don't have an index in the tables and I don't know how to create one.

This is a very big panel and the records grow up every day by at least 1000 records every day.

Omry Lieber
  • 37
  • 6
  • Please post the `SHOW CREATE TABLE tablename` output for each of the tables used in this query. Not having foreign keys defined to enforce integrity doesn't matter, but having FKs does create the index for you even on MyISAM. I would not expect InnoDB to make a difference, but indexing would likely make an enormous difference. With the `SHOW CREATE TABLE` output, it will help us to suggest the right indexes. – Michael Berkowski Nov 22 '15 at 13:01
  • can you add table structure of each of the table in the query !! – saurabh kamble Nov 22 '15 at 13:01
  • And please let us know what the source of the variable `$dates` is. If that were already a unix timestamp instead of a string, it could save considerable resources in your WHERE clause. – Michael Berkowski Nov 22 '15 at 13:04
  • Do you have indexes added to the columns that you are searching for ? – Sarath Chandra Nov 22 '15 at 13:05
  • @SarathChandra "_I don't have an index in the tables and I don't know how to create one_" last point above. – Michael Berkowski Nov 22 '15 at 13:05
  • Hi, I added the table structure – Omry Lieber Nov 22 '15 at 13:58

0 Answers0