MySql Table Index

Indexes are used to find rows with specific column values faster.
without an index MYSQL has to start with the first record and then read through the whole table to find the relevant rows. if the table is larger then it costs performance.
If index is defined on a table column (e.g named mtype in module table), then mysql will determine the postion in the middle of the data file accroding to your where clause in query, insted of looking at the whole data file.
PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT are the indexes mostly used in mysql.
In general indexes are used
  • To quickly find the rows that match a WHERE clause
  • To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
    • example
      mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
      If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.
  • If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows.
    • e.g
    if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
    MySQL can’t use a partial index if the columns don’t form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:
    SELECT * FROM tbl_name WHERE col1=val1;
    SELECT * FROM tbl_name WHERE col2=val2;
    SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
    If an index exists on (col1, col2, col3), only the first of the preceding queries uses the index. The second and third queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).
    The following SELECT statements will not use indexes:
    SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
    SELECT * FROM tbl_name WHERE key_col LIKE other_col;
    In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

    Comments

    Post a Comment

    Popular posts from this blog

    How to call php functions inside smarty template directly

    we can use {insert} tag for calling php functions inside smarty templates directly to display the output of the php function. it is similar to {include} tag except that {insert} tags are NOT cached when template caching is enabled. They will be executed on every invocation of the template. Remember all {insert} function names in your application must be prepended with “insert_” to remedy possible function name-space conflicts.so simply if your function is named as insert_getBanner, then call it inside {insert} tag as {insert name=”getBanner”} {* example of fetching a banner with agruments*} {insert name=”getBanner” lid=#banner_location_id# sid=#site_id#} Here we call a php function named as insert_getBanner with arguments lid and site_id so the php function must as like function insert_getBanner( $lid, $site_id) { ….. } we can use assign and script attributes to assign the output of insert tag to a variable and including a php script. more details can be found on http
    Read more

    Top 50 Web Hacking Techniques

    This post will serve to collect new attack techniques as they are published. If you think something should be added, please comment below and I'll add them. "Every year the Web security community produces a stunning amount of new hacking techniques published in various white papers, blog posts, magazine articles, mailing list emails, etc. Within the thousands of pages are the latest ways to attack websites, Web browsers, Web proxies, and so on. Beyond individual vulnerability instances with CVE numbers or system compromises, we're talking about actual new and creative methods of Web-based attack. The Top Ten Web Hacking Techniques list encourages information sharing, provides a centralized knowledge-base, and recognizes researchers who contribute excellent work." Current 2011 List Bypassing Flash’s local-with-filesystem Sandbox Abusing HTTP Status Codes to Expose Private Information SpyTunes: Find out what iTunes music someone else has CSRF: Fla
    Read more

    PHP / SQL Security – The Big Picture

    Web Security Whether your site is the web presence for a large multinational, a gallery showing your product range and inviting potential customers to come into the shop, or a personal site exhibiting your holiday photos, web security matters. After the hard work put in to make your site look good and respond to your users, the last thing you want is for a malicious hacker to come along, perform a PHP hack and break it somehow. There are a number of problems in web security, and unfortunately not all of them have definite solutions, but here we'll look at some of the problems that should be considered every time you set out to write a PHP script to avoid a PHP hack attack. These are the problems which, with well-designed code, can be eliminated entirely. Before looking in detail at the solutions, though, lets take a moment to define the problems themselves. SQL Injection In this attack, a user is able to execute SQL queries in your website's database. This attack is u
    Read more