Help Optimizing MySQL and Apache - Server been running for 8 days now

djblamire

Well-Known Member
May 3, 2003
255
1
168
I have just purchased a new dedicated server and currently using the defaults for MySQL and Apache. I'm looking to try and optimize the setup as best I can.

Here is the spec of the machine:

Intel Core i7-2600 Quad Core
8GB DDR3 RAM
CentOS 6.4 - 64-Bit

The spec is better than my previous machine, but the server load seems to be generally higher - I did do some tweaks on the previous machine, but I no longer have access to that machine to compare.

I'm not entirely sure what files I should be providing with regards to Apache - in order to optimize, but here are the ones for MySQL.

Here is the contents of /etc/my.cnf

Code:
[mysqld]
innodb_file_per_table=1
local-infile=0
open_files_limit=2090
I have run /usr/bin/mysqltuner.pl and this is what is showing - Everything has been running as it is for a tleast 8 days now:

Code:
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 67M (Tables: 569)
[--] Data in InnoDB tables: 44M (Tables: 935)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 19

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10d 19h 41m 26s (6M q [7.023 qps], 968K conn, TX: 21B, RX: 1B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (7% of installed RAM)
[OK] Slow queries: 0% (0/6M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/21.1M
[OK] Key buffer hit rate: 99.5% (7M cached / 40K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 4801
[!!] Temporary tables created on disk: 27% (60K on disk / 215K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 141K opened)
[OK] Open file limit used: 13% (279/2K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[OK] InnoDB data size / buffer pool: 44.4M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 400)
I don't quite understand exactly what it is recommending.

Any help would really be appreciated with this.

Thanks in advance,

Daniel
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
add in the end of my.cnf
and change open_files_limit=2090 to open_files_limit=10000
myisam_use_mmap=1
connect_timeout = 2
join_buffer_size=2M
read_rnd_buffer_size=4M
query_cache_type = 1
query_cache_size = 30M
query_cache_limit = 1M
max_heap_table_size = 50M
tmp_table_size = 50M
thread_cache_size = 50
table_open_cache = 1000
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
key_buffer_size = 500M
concurrent_insert=2

and restart mysql
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
Thank you very much for your help. - I've now applied those changes.

Is there anything I should now be monitoring to check for improvement ? - Log files, re-running the mysqltuner command ?

Looking at the mysql-slow.log file seems to show the majority of queries are being logged. Most so far seem to be between about 0.1066 and 0.1366.

Also, in terms of optimizing Apache, what files do I need to provide for anyone to be able to provide a bit of assistance with that ?

Thanks in advance,

Daniel
 
Last edited:

djblamire

Well-Known Member
May 3, 2003
255
1
168
After running the server for over 24 hours since the changes were implemented, here are the results of running the mysqltuner command again now:

Code:
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 70M (Tables: 569)
[--] Data in InnoDB tables: 44M (Tables: 935)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 19

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 21m 1s (1M q [10.871 qps], 141K conn, TX: 2B, RX: 201M)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 724.0M global + 8.4M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.9G (26% of installed RAM)
[!!] Slow queries: 15% (162K/1M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 500.0M/21.8M
[OK] Key buffer hit rate: 98.1% (212K cached / 4K reads)
[OK] Query cache efficiency: 60.6% (352K cached / 581K selects)
[!!] Query cache prunes per day: 47990
[OK] Sorts requiring temporary tables: 0% (361 temp sorts / 175K sorts)
[!!] Joins performed without indexes: 1077
[!!] Temporary tables created on disk: 27% (4K on disk / 16K total)
[OK] Thread cache hit rate: 99% (11 created / 141K connections)
[!!] Table cache hit rate: 5% (1K open / 19K opened)
[OK] Open file limit used: 7% (711/10K)
[OK] Table locks acquired immediately: 99% (309K immediate / 310K locks)
[OK] InnoDB data size / buffer pool: 44.4M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 30M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)
    table_cache (> 1000)
Can you recommend any other improvements, or does that look a lot better now ?

With regards to the Apache 'tweaking', here is part of the httpd.conf that I think may be the useful bit ?

Code:
# These can be set in WHM under 'Apache Global Configuration'
Timeout 60
TraceEnable Off
ServerSignature Off
ServerTokens ProductOnly
FileETag None
StartServers 5
<IfModule prefork.c>
MinSpareServers 5
MaxSpareServers 10
</IfModule>
ServerLimit 256
MaxRequestWorkers 150
MaxConnectionsPerChild 10000
KeepAlive Off
KeepAliveTimeout 5
MaxKeepAliveRequests 100

RewriteEngine on
RewriteMap LeechProtect prg:/usr/local/cpanel/bin/leechprotect
Mutex file:/usr/local/apache/logs rewrite-map
Thanks in advance for any assistance,

Daniel
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Apache is good, you can lower Timeout, and start a bit more servers at once

Timeout 30
StartServers 10
MinSpareServers 5
MaxSpareServers 10

about mysql, it's good,
now you need to optimize the queries from mysql-slow.log
describe what script you use (is it custom etc)

run pt-query-digest to see slow queries review
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
Thanks again for your help - I'll take a look at the apache.

The script was custom built (several years ago), and I've lost track how we put it all together.

There are only two types of queries being used (one each of two pages), but here is the page code for one of them:

I believe it is probably very badly written, but with only knowing a small amount of MySQL/PHP it is hard for me to improve on.

Code:
<?php require_once('Connections/databaseconnect.php'); ?>
<?php require('functionformat.php'); ?>
<?php

/*
The following strips out the use of the % and _ wildcard characters, before running the query.
*/

$pattern = array('/%/','/_/','/!/','/$/','/^/','/{/','/}/','/,/','/=/','/"/','/|/','/:/','/;/');
$replace = array('','');
$search_name = preg_replace($pattern, $replace, $_POST[search_name]);

$search_name = mysql_real_escape_string($search_name);

$unconfirmed_header = 0;
$confirmed_header = 0;

if (strlen($search_name) < "2") {

}

else {

$maxRows_Recordset1 = 1250;
$pageNum_Recordset1 = 0;

if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_stemel, $stemel);
$query_Recordset1 = "SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1 WHERE (confirmed = '0' OR confirmed = '1') AND (company_name LIKE '%$search_name%' OR other_info 

LIKE '%$search_name%' OR url LIKE '%$search_name%') ORDER BY confirmed DESC, company_name ASC, company_number ASC";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stemel) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1); 
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

}
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<META NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW">
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />

<?php
/* This section passes on received values as hidden fields after validating null entries. */
include ('header.php');
?>

<link rel="stylesheet" href="search.css" type="text/css" />

  <?php
if (($totalRows_Recordset1) > 0) { 

		if ($row_Recordset1['confirmed'] & 1) { 
		
		?>
<table width="100%" align="center" border="0">
  <tr>
    <td><font size="2">

<div class="seperator"></div>

<div class="boardcontainer">
<table cellpadding="4" cellspacing="1" border="0" width="100%">
<tr><td colspan="6" class="catbg" height="18" >Main Database</td></tr>

<tr>
	<td class="windowbg" width="28%" align="center">Company Name</td>
	<td class="windowbg" width="12%" align="center">Phone No</td>
	<td class="windowbg" width="12%" align="center">Mobile No</td>
	<td class="windowbg" width="12%" align="center">Home No</td>
	<td class="windowbg" width="12%" align="center">Personal Mobile</td>
	<td class="windowbg" width="24%" align="center">Other Info</td>
</tr>

<?php 
$confirmed_header=1;
} 

/*
Message that is displayed when no CONFIRMED numbers are returned from the database
*/

				{				


if ($confirmed_header < 1)
{

			?>	
			<table width="100%" align="center" border="0">
			<tr>
		    <td><font size="2">

			<div class="seperator"></div>

			<div class="boardcontainer">
			<table cellpadding="4" cellspacing="1" border="0" width="100%">
			<tr><td colspan="6" class="catbg" height="18" >Main Database</td></tr>

			<tr>
				<td class="windowbg" width="28%" align="center">Company Name</td>
				<td class="windowbg" width="12%" align="center">Phone No</td>
				<td class="windowbg" width="12%" align="center">Mobile No</td>
				<td class="windowbg" width="12%" align="center">Home No</td>
				<td class="windowbg" width="12%" align="center">Personal Mobile</td>
				<td class="windowbg" width="24%" align="center">Other Info</td>
			</tr>


  <tr>
    <td class=windowbg2 colspan="6" align=center BGCOLOR=#FFFFCC><B>No confirmed numbers have been found that match the criteria you entered.  One or more have been found in the unconfirmed list, and are listed below.</B></td>
  </tr>


</table>
</div>
				<?php
				}{}
				} ?>
  <?php do { ?>
  <tr>
<?php
     if ($row_Recordset1['confirmed'] & 1) { 

				if (strlen($row_Recordset1['url']) > "2") {
						 	 echo '<td class=windowbg2 width=28% align=center BGCOLOR=#FFFFCC><a href=http://www.mywebsite.com/external.php?site='.$row_Recordset1['url'].' target="_blank">'.$row_Recordset1

['company_name'].'</a></td>';   } else {
		 
		   echo '<td class=windowbg2 width=28% align=center BGCOLOR=#FFFFCC>'.$row_Recordset1['company_name'].'</a></td>';		 
		 }
      echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['phone_no']).'</td>';
     echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mobile_no']).'</td>';
     echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['home_no']).'</td>';
     echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mob2_no']).'</td>';
     echo '<td class=windowbg2 width=24% align=center BGCOLOR=#FFFFCC>*'.$row_Recordset1['other_info'].'</td></tr>';
        } else { 
	if ($unconfirmed_header < 1) {
?>

</tr>
</table>
</div><br />	

<table width="100%" align="center" border="0">
  <tr><td><font size="2">
<div class="seperator"></div>

<div class="boardcontainer">
<table cellpadding="4" cellspacing="1" border="0" width="100%">

<tr><td colspan="6" class="catbg" height="18" >Unconfirmed Numbers</td></tr>

<tr>
	<td class="windowbg" width="28%" align="center">Company Name</td>
	<td class="windowbg" width="12%" align="center">Phone No</td>
	<td class="windowbg" width="12%" align="center">Mobile No</td>
	<td class="windowbg" width="12%" align="center">Home No</td>
	<td class="windowbg" width="12%" align="center">Personal Mobile</td>
	<td class="windowbg" width="24%" align="center">Other Info</td>
</tr>

<?php
	$unconfirmed_header = 1;
				}

				if (strlen($row_Recordset1['url']) > "2") {
						 	 echo '<td class=windowuv width=28% align=center BGCOLOR=#CCFFFF> '.$row_Recordset1['company_name'].'</td>';   } else {
		 
		   echo '<td class=windowuv width=28% align=center BGCOLOR=#CCFFFF>'.$row_Recordset1['company_name'].'</a></td>';
		 		 
		 }

     echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['phone_no']).'</td>';
     echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['mobile_no']).'</td>';
     echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['home_no']).'</td>';
     echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['mob2_no']).'</td>';
     echo '<td class=windowuv width=24% align=center BGCOLOR=#CCFFFF>*'.$row_Recordset1['other_info'].'</td></tr>';
        } 
?>

  <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

</tr>
</table>
</div><br />

<div class="seperator">
<table cellpadding="4" cellspacing="1" border="0" width="100%">
<tr>
	<td class="titlebg" align="center" colspan="2">
		Info Centre
	</td>
</tr>

	<td class="windowbg2">
		<div style="float: left; width: 59%; text-align: left;">
		<span class="small">Please update us with any corrections as soon as possible.</span><br />
		</div>
		<div style="float: left; width: 40%; text-align: left;">
		<div class="small" style="float: left; width: 49%;"><span style="color: red;"><b>lllll</b></span></div>
		</div>
	</td>
</tr>
</table>
</div>
    </font></td>
  </tr>
</table>

<?php } else { ?>

<body bgcolor="#FFFFCC">
	<tr><td colspan=10>
	 <center><table border=1 bordercolor=navy cellpadding=0 cellspacing=0><tr><td bgcolor="#CCCCFF"><div class=TableTitle>
		<p align="center"><b><font face="Tahoma">NO RESULTS FOUND</font></b></div></td></tr><tr><td BGCOLOR=#FFFFCC>
<div align="center">
	<h3>&nbsp;</h3>
</div>
<div align="center">
	<h3><font face="Tahoma" size="3">Your search didn't match any records.</B></font></h3>

	<h3>&nbsp;</h3>
</div>
</td></tr></table></center>
</td></tr></table><br>

<?php
}
?>
<br>
<?php
require ('footer.php');

if (($totalRows_Recordset1) > 0) { 
mysql_free_result($Recordset1);
mysql_close();
}else{ 
} 
?>
Thanks
Daniel
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
LIKE %key% can't utilize indexes, its slow row by row scan, full text search will be better

btw. you can run this
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

and in slow.txt will be the detailed review of slow queries which are consuming the most time
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
Hi,

Thanks - I'll have a look into 'Full Text' and see how easy it would be to change the current setup.

With regards to 'pt-query-digest', I've just installed and run that, but not entirely sure what the output is trying to tell me.

Code:
# 28.8s user time, 110ms system time, 33.29M rss, 186.64M vsz
# Current date: Tue Jul 30 09:40:43 2013
# Hostname: server.myserver.com
# Files: /var/lib/mysql/mysql-slow.log
# Overall: 176.44k total, 34 unique, 1.20 QPS, 0.14x concurrency _________
# Time range: 2013-07-28 16:44:19 to 2013-07-30 09:40:43
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         20576s   100ms      7s   117ms   122ms    20ms   116ms
# Lock time            18s       0    78ms   101us   138us   234us    93us
# Rows sent          9.38M       0  87.80k   55.76  246.02  582.64    1.96
# Rows examine      14.39G       0 120.50k  85.55k  83.83k   3.54k  83.83k
# Query size        86.04M      14   1.40k  511.31  621.67  147.90  621.67

# Profile
# Rank Query ID           Response time   Calls R/Call V/M   Item
# ==== ================== =============== ===== ====== ===== =============
#    1 0x63458FE4D3477429 7458.0904 36.2% 62485 0.1194  0.00 SELECT contact?
#    2 0x0E1182D2854678D1 5007.8278 24.3% 42941 0.1166  0.00 SELECT contact?
#    3 0x8AA02BDCA2A87600 4908.9373 23.9% 42763 0.1148  0.00 SELECT contact?
#    4 0x503B590421C28943 3141.6332 15.3% 27919 0.1125  0.00 SELECT contact?
# MISC 0xMISC               59.6004  0.3%   335 0.1779   0.0 <30 ITEMS>

# Query 1: 0.42 QPS, 0.05x concurrency, ID 0x63458FE4D3477429 at byte 39730472
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2013-07-28 16:44:26 to 2013-07-30 09:40:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         35   62485
# Exec time     36   7458s   106ms   558ms   119ms   128ms     7ms   116ms
# Lock time     40      7s    41us    12ms   117us   144us    67us   131us
# Rows sent      5 553.61k       0     500    9.07   24.84   40.34    0.99
# Rows examine  35   5.10G  85.61k  86.18k  85.66k  83.83k       0  83.83k
# Query size    44  38.12M     618     649  639.77  621.67    1.38  621.67
# String:
# Databases    server5_contactdb
# Hosts        localhost
# Users        server5_contactdb
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
#    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1
WHERE
((phone_no LIKE '09653601000') OR
(mobile_no LIKE '09653601000') OR
(home_no LIKE '09653601000') OR
(mob2_no LIKE '09653601000') OR
(other_info LIKE '%09653601000%') OR
(other_info LIKE '%0965 3601000%') OR
(other_info LIKE '%0965 360 1000%') OR
(other_info LIKE '%09653 601000%') OR
(other_info LIKE '%09653 601 000%') OR
(other_info LIKE '%096 53601000%') OR
(other_info LIKE '%096 5360 1000%')) AND (confirmed = '0' OR confirmed = '1')
ORDER BY confirmed DESC, company_name ASC, company_number ASC LIMIT 0, 500\G

# Query 2: 0.29 QPS, 0.03x concurrency, ID 0x0E1182D2854678D1 at byte 39729993
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2013-07-28 16:44:19 to 2013-07-30 09:40:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         24   42941
# Exec time     24   5008s   100ms   593ms   117ms   128ms    10ms   110ms
# Lock time     23      4s    32us    11ms    96us   119us    86us   103us
# Rows sent     43   4.08M       0   1.22k   99.65  685.39  236.75    7.70
# Rows examine  24   3.51G  85.61k  86.91k  85.75k  83.83k       0  83.83k
# Query size    15  13.75M     305     545  335.65  363.48   20.02  329.68
# String:
# Databases    server5_contactdb
# Hosts        localhost
# Users        server5_contactdb
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
#    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1
WHERE (confirmed = '0' OR confirmed = '1') AND (company_name LIKE '%Apple%' OR other_info LIKE '%Apple%' OR url LIKE '%Appl$

# Query 3: 0.51 QPS, 0.06x concurrency, ID 0x8AA02BDCA2A87600 at byte 39732255
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2013-07-28 16:44:26 to 2013-07-29 15:49:27
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         24   42763
# Exec time     23   4909s   104ms   551ms   115ms   122ms     7ms   110ms
# Lock time     22      4s    38us    37ms    94us   131us   190us    89us
# Rows sent      4 419.93k       0     658   10.06   28.75   46.31    0.99
# Rows examine  24   3.49G  85.61k  86.31k  85.65k  83.83k       0  83.83k
# Query size    29  25.56M     605     636  626.77  621.67    5.64  621.67
# String:
# Databases    server5_contactdb
# Hosts        localhost
# Users        server5_contactdb
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
#    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1
WHERE
((phone_no LIKE '09772001632') OR
(mobile_no LIKE '09772001632') OR
(home_no LIKE '09772001632') OR
(mob2_no LIKE '09772001632') OR
(other_info LIKE '%09772001632%') OR
(other_info LIKE '%0977 2001632%') OR
(other_info LIKE '%0977 200 1632%') OR
(other_info LIKE '%09772 001632%') OR
(other_info LIKE '%09772 001 632%') OR
(other_info LIKE '%097 72001632%') OR
(other_info LIKE '%097 7200 1632%')) AND (confirmed = '0' OR confirmed = '1')
ORDER BY confirmed DESC, company_name ASC, company_number ASC\G

# Query 4: 0.34 QPS, 0.04x concurrency, ID 0x503B590421C28943 at byte 39729533
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2013-07-28 16:44:19 to 2013-07-29 15:45:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15   27919
# Exec time     15   3142s   100ms   630ms   113ms   122ms    11ms   110ms
# Lock time     12      2s    28us    33ms    80us   108us   210us    73us
# Rows sent     40   3.85M       0  34.89k  144.46  755.64  672.87    8.91
# Rows examine  15   2.28G  85.61k 120.50k  85.78k  83.83k  683.98  83.83k
# Query size     9   8.55M     291     531  321.07  346.17   19.89  313.99
# String:
# Databases    server5_contactdb
# Hosts        localhost
# Users        server5_contactdb
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
#    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1 WHERE (confirmed = '0' OR confirmed = '1') AND (company_name LIKE '%microsoft%' OR other_info LIKE '%microsoft%' OR url LIKE '%microsoft%') O$
I really appreciate all the help you have provided so far. Any help and guidance on the above would really be appreciated.

Thanks
Daniel
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
The simplest way to do this is adding simple indexes on phone_no, mobile_no, home_no, mob2_no etc
and full text index on other_info
and another full index on company name

then add full text search in your query instead of like %key%

MySQL will pick the best index for the use

this is the simplest optimization that can be done without working on the database (checking the data and it's structure)

and instead of using WHERE (confirmed = '0' OR confirmed = '1')
use WHERE confirmed IN (0, 1)
 

djblamire

Well-Known Member
May 3, 2003
255
1
168
Thanks again for the recommended changes.

When you say 'adding simple indexes' is this done in the structure of the MySQL database ?

Will using "WHERE confirmed IN (0, 1)" actually be more efficient than using "WHERE (confirmed = '0' OR confirmed = '1')", or is it just more about the right way of doing it ?

Thanks again,
Daniel