We should Keep in mind the followings when we use group by in custom queries.
ex: SELECT pname, MIN(price) FROM products GROUP BY pname;
1. The column that you GROUP BY must also be in your SELECT statement.
2. Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the pname and the aggregate function was applied to the price column.
Friday, October 30, 2009
More Mysql Indexes affect performance on heavy traffic site
I want to share about index on table columns. Indexes are something extra that you can enable on your MySQL tables to increase performance, but they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance. So avoid using index unnecessarily. That will affect the performance.
Thursday, August 20, 2009
Check the user submitting date
checkdate() is a function to check the date is valid or not.
eg:
var_dump(checkdate(12, 31, 2000)); // return true
var_dump(checkdate(2, 29, 2001)); // return false
eg:
var_dump(checkdate(12, 31, 2000)); // return true
var_dump(checkdate(2, 29, 2001)); // return false
Tuesday, June 2, 2009
Drupal watchdog query into file
To avoid record growing in watchdog table I am doing the followings. outfile in mysql is used to write output in a file.
I am using following code to write watchdog output into file.
<?php
function customfunction() {
$datetime = date("Y_m_d_Hi");
$path = "c:/";
db_query("SELECT wid,type,uid,message,severity,link,location,referer,hostname,timestamp FROM watchdog ORDER BY wid DESC LIMIT 0,10 INTO OUTFILE '".$path."watchdog".$datetime.".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' ");
}
?>
I am using following code to write watchdog output into file.
<?php
function customfunction() {
$datetime = date("Y_m_d_Hi");
$path = "c:/";
db_query("SELECT wid,type,uid,message,severity,link,location,referer,hostname,timestamp FROM watchdog ORDER BY wid DESC LIMIT 0,10 INTO OUTFILE '".$path."watchdog".$datetime.".csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' ");
}
?>
Friday, May 29, 2009
Implode in Javascript - Method 1
toString() method returns one string that contains all the elements in the array separated with commas.
The toString() method is used to convert an array to a string when the array is used in string context.
In this method we cannot modify the seperator. To change seperator go to join().
The following example shows JavaScript to Use an Array's toString() Method.
<html>
<script language="JavaScript">
colors = new Array("Blue","Green","Red");
document.write(colors.toString());
</script>
</html>
Output is Blue,Green,Red
The toString() method is used to convert an array to a string when the array is used in string context.
In this method we cannot modify the seperator. To change seperator go to join().
The following example shows JavaScript to Use an Array's toString() Method.
<html>
<script language="JavaScript">
colors = new Array("Blue","Green","Red");
document.write(colors.toString());
</script>
</html>
Output is Blue,Green,Red
Implode in Javascript - Method 2
To make implode in javascript like php use join method.The join() method converts all the elements to strings and then concatenates all the strings into a longer string.
If an argument is provided in the parameter list, it is used to separate the elements in the string returned by the method.
Ex:
<html>
<script language="JavaScript">
fruit = new Array("1","2","3");
String = fruit.join("-");
document.write("The fruit array contains: ",String);
</script>
</html>
Output : 1-2-3
If an argument is provided in the parameter list, it is used to separate the elements in the string returned by the method.
Ex:
<html>
<script language="JavaScript">
fruit = new Array("1","2","3");
String = fruit.join("-");
document.write("The fruit array contains: ",String);
</script>
</html>
Output : 1-2-3
Wednesday, May 20, 2009
Steps to install memcache in drupal
1.Install the memcached binaries on your server. See How to install Memcache on Debian Etch[ http://www.lullabot.com/articles/how_install_memcache_debian_etch ] or How to install Memcache on OSX [ http://www.lullabot.com/articles/setup-memcached-mamp-sandbox-environmen... ]
2.Install the PECL memcache extension for PHP.
3. In settings.php add ini_set('memcache.hash_strategy','consistent');
4. Put your site into offline mode.
5. Download and install the memcache module [ http://drupal.org/project/memcache ]
6. If you have previously been running the memcache module, run update.php.
7. Apply the DRUPAL-5-cache-serialize.patch that comes with memcache module [memcache/patches]
I applied DRUPAL-5-3-cache-serialize.patch hence I am using drupal 5.3
8. Start at least one instance of memcache on the server.
[ex ./memcached -d -m 2048 -l 10.0.0.40 -p 11211 ]
Here 11211 is an instance of memcache, 10.0.0.40 is the IP of the server, 2048 is the allocated memory.
9. Add following in the settings.php
$conf = array(
'cache_inc' => './sites/all/modules/memcache/memcache.inc',
'memcache_servers' => array(
'localhost:11211' => 'default',
),
'memcache_bins' =>array(
'cache' => 'default',
'cache_views' => 'default',
'cache_page' => 'default',
'cache_path' => 'default',
'cache_filter' => 'default',
'cache_menu' => 'default',
),
);
10.Change memcache_stampede_semaphore time from 15 seconds to 600 in dmemcache.inc lineno:86
if ($result->expire && $result->expire <= time() && $mc->add($full_key .'_semaphore', '', FALSE, variable_get('memcache_stampede_semaphore', 600))) {
located in modules/memcache to get clear result of memcache.
Every 600 seconds cache from memcache will be cleared. If this is not set we will not see any performance benefit. Refer links… http://drupal.org/files/issues/stampede.patch. http://drupal.org/node/295738.
11. First time after install, truncate all cache tables that are memcached.
12. Bring your site back online.
13. To check the memcache status login as admin and check memcahce logs at /admin/logs/memcache.
Subscribe to:
Posts (Atom)