Skip to main content

MySql Indexing

MySql Indexing

A database index is a data structure that improves the speed of operations in a table.INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to insert or update index values as well.

Indexes are a way to avoid scanning the full table to obtain the result
CREATE TABLE person (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
INDEX (last_name, first_name)
);
So if your index has two columns, say last_name and first_name, the order that you query these fields matters a lot.


This query would take advantage of the index:

SELECT last_name, first_name FROM person WHERE last_name = "John" AND first_name LIKE "J%"

Using Create statement
CREATE INDEX techsudhir_age ON employees(emp_name, emp_age);

Alter command to update index
ALTER TABLE person  ADD INDEX (name);

Removing Indexes
DROP INDEX name ON person 

In MySQL, the full-text index is a kind of index that has a name FULLTEXT. It define for a column whose data type is CHAR, VARCHAR or TEXT
Create FULLTEXT index

CREATE TABLE person (
id int(4) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
post_content text,
PRIMARY KEY (id),
FULLTEXT KEY emp_name(last_name, first_name),
FULLTEXT KEY post_content (post_content)
);

FULL TEXT INDEX

Full-text is a “natural language search”.It indexes words that appear to represent the row, using
the columns you specified.

  1. FULLTEXT is the index type of full-text index in MySQL.
  2. Full-text indexes can be created only for VARCHAR, CHAR or TEXT columns.

Alter command
ALTER TABLE person ADD FULLTEXT KEY post_content (post_content)
OR you can also create using create command

CREATE FULLTEXT INDEX post_content ON person(last_name, first_name)

ALTER TABLE post_content DROP INDEX keywords;

Syntax : MATCH (col1,col2,col3...) AGAINST (expr [search_modifier])

//AGAINST() takes a string to search, and an optional modifier that indicates what type of search to perform.The search string must be a string value.

SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP');

Types of full-text searches :

Natural Language Full-Text Searches:
Natural language full-text search interprets the search string as a free text and no special operators are required.
Syntax : AGAINST (expr  IN NATURAL LANGUAGE MODE)
Example: SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP' IN NATURAL LANGUAGE MODE);

Boolean Full-Text searches: It allow you to proceed words with a + or a - to force it to either be present (+) or not present (-).
Syntax : AGAINST (expr  (+)exp IN BOOLEAN MODE)
Example: SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP -CakePHP'  IN BOOLEAN MODE);
"-CakePHP" means that MySQL will not return any rows that match "CakePHP", even if they match "PHP".

Putting double quotes around groups of words allow phrase searching. 
Example: SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('"PHP CakePHP"'  IN BOOLEAN MODE);

That matches rows that have "PHP CakePHP" just like that - no words in between, not one or the other.

Querying the Data
mysql> SELECT entryID,title ROM blog_entries WHERE MATCH (title,entry) AGAINST('mother');

Comments

Popular posts from this blog

6 guaranteed steps how to create CRON JOB FUNTION in wordpress

Create Cron Job function in Wordpress plugin Step 1: Register function on plugin activate  register_activation_hook(__FILE__, 'activate_one'); Step 2: add_filter function for interval //Filter for Adding multiple intervals add_filter( 'cron_schedules', 'intervals_schedule' ); // define interval function   function intervals_schedule($schedules) {   $schedules['everyminute'] = array(    'interval' => 60, // Every 1 minutes    'display'  => __( 'Every 1 minutes' )   );   return $schedules;  } Step 3: Activate hook function   //Schedule a first action if it's not already scheduled   function activate_one() {   if (!wp_next_scheduled('wpyog_cron_action')) {    wp_schedule_event( time(), 'everyminute', 'wpyog_cron_action');   }  } Step 4: Cron hook function   //Hook into that action that'll fire after 1 minutes   add_action('wpyog_cron_action', 'execute_

How to Create a jQuery Autocomplete in Wordpress

How to Create a jquery-ui Autocomplete in wordpress. Autocomplete provides suggestions while you type into the text field. In Wordpress we fetch dynamically matched pattern. Include javascript and css files in header. Create action inside functions.php or inside plugin code. add_action('wp_head', 'custom_register_scripts'); function custom_register_scripts(){ wp_register_style( 'techsudhir_jquery_ui_css', plugin_dir_url(__FILE__) . 'css/jquery-ui.css', false,'1.0.0' ); wp_enqueue_style( 'techsudhir_jquery_ui_css' ); wp_register_script('techsudhir_jquery_ui_js',plugin_dir_url(__FILE__) . 'js/jquery-ui.js',array('jquery'),'1.1', false); wp_enqueue_script('techsudhir_jquery_ui_js'); wp_localize_script( 'techsudhir_autocomplete', 'jqueryAutocomplete', array( 'ajaxurl' => admin_url( 'admin-ajax.php' ) ) ); wp_enqueue_script( 'techsudhir_

Facebook Login

Step 1: Go on Apps & create new Apps Step 2: Enter the app name & choose category Step 3: Enter the site domain Script code <div id="fb-root"></div> <script   src="http://connect.facebook.net/en_US/all.js#appId=<?php echo '603609636351597';?>"> </script> <script>    FB.init({              appId:'<?php echo '603609636351597';?>', cookie:true,              status:true, xfbml:true           });   FB.getLoginStatus(function(response) {   }); Login function call on click function faceLogin(){  FB.login(function(response) {    if (response.authResponse) {  FB.api('/me', function(response) { $('input[id=facebook_login_fb]').val(response['id']); $('#fb_login_form').submit();  });    } else {  console.log('User cancelled login or did