Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 13 3 Older Entries

Displaying posts with tag: queries (reset)

How To Perform Basic Queries of MySQL / MariaDB on Fedora 20 For Newbies !!
+0 Vote Up -0Vote Down
Following Article Describes How To Perform Most Basic MYSQL / MariaDB Queries on Fedora 20? or How to Run MySQL on Fedora 20 ? Which is Latest Version of Linux Based Operating System Project. Last Operation of This Assignment also describes How to change MySQL 'Root' User password on Linux.
It is also a part of 3rd Year, 5th sem Computer Engineering Academic Curriculum of Pune University. As PL 1 Subject. Group A, Assignment 1. It's Problem Statement And Solution is Given Below. You may also Checkout Database Management System complete syllabus & Tutorial of 5th sem subject called Database Management systems application.

Read more »
Building Queries Systematically
Employee +0 Vote Up -0Vote Down

The SQL language is a bit like a toolkit for data. It consists of lots of little fiddly bits of syntax that, taken together, allow you to build complex edifices and return powerful results. For the uninitiated, the many tools can be quite confusing, and it's sometimes difficult to decide how to go about the process of building non-trivial queries, that is, queries that are more than a simple SELECT a, b FROM c;

A System for Building Queries

When you're building queries, you could use a system like the following: 

  • Decide which fields contain the values you want to use in our output, and how you wish to alias those fields
  • Values you want to see in your output
  • Values you want to use in calculations . For example, to calculate margin on a product, you could
  •   [Read more...]
    What kind of queries are bad for MySQL?
    +0 Vote Up -1Vote Down

    In writing a recommendation for our Web development team on how to use MySQL, I came up with the following list, which I want to share: What kind of queries are bad for MySQL?

    • Any query is bad. Send a query only if you must. (Hint: use caching like memcache or redis)
    • Queries that examine many rows are bad. Try instead to use…
      SELECT col1 FROM table1 WHERE primary_key_column=SOMETHING

      . Or at least
      secondary_key_column=SOMETHING

      . If it is still not possible, try to make the query examine the least amount of rows possible (zero is ideal, as we come to the first case here)
    • Queries with JOINS are bad. Try to denormalize the table to avoid JOINS. Example: original query
      SELECT t2.value FROM t2 JOIN t1 ON (t1.id=t2.tid) WHERE t1.orderdate=NOW()

      . This can be denormalized





      [Read more...]
    Get the database size, free space and last update
    +0 Vote Up -0Vote Down
    To get the current database size just by querying into your query browser or CLI from the INFORMATION_SCHEMA database in table TABLES.

    SELECT table_schema "Data Base Name",
    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
    FROM information_schema.TABLES
    GROUP BY table_schema ;

    Get the database free space

    SELECT table_schema "Data Base Name",
    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    sum( data_free )/ 1024 / 1024 "Free Space in MB"
    FROM information_schema.TABLES
    GROUP BY table_schema;

    Get the database last update ordered by update time then by create time.

    SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA
    FROM `TABLES`
    GROUP BY TABLE_SCHEMA
    ORDER

















      [Read more...]
    Profiling your slow queries using pt-query-digest and some love from Percona Server
    +2 Vote Up -0Vote Down

    This guide will get you up and running with how to identify the bottleneck queries using the excellent tool pt-query-digest. You will learn how to use and analyze the output returned by pt-query-digest. You will also learn some differences between slow query logging in various MySQL versions. Later on in the post I will also show you how to make use of the extra diagnostic data available with Percona Server.

    The post Profiling your slow queries using pt-query-digest and some love from Percona Server appeared first on ovais.tariq.

    MySQL Paginated displays – How to kill performance vs How to improve performance!
    +0 Vote Up -0Vote Down
    Pagination is used very frequently in many websites, be it search results or most popular posts they are seen everywhere. But the way how it is typically implemented is naive and prone to performance degradation. In this article I attempt on explaining the performance implications of poorly designed pagination implementation. I have also analyzed how Google, Yahoo and Facebook handle pagination implementation. Then finally i present my suggestion which will greatly improve the performance related to pagination.
    The “Shadow Table” trick.
    +0 Vote Up -0Vote Down
    The need: Often there is a requirement where data in a particular table has to be processed, and the data processing might be slow, while the table might be a one that is used by your application extensively. For example, a logging table that logs page hits. Or there might be an archiving operation that has to be performed on a particular table. Archiving / processing / aggregating records, all these operations are slow and can really blog down a website, combine that with the added overhead if the table that needs to have these operations performed is one that...
    Performance tuning using vertical partitioning.
    +0 Vote Up -0Vote Down
    Does having small data-sets really help? Of course it does! Are memory lookups faster that disk lookups. Of course ! So many times I have seen people complain about queries taking too long now, while they were not taking that long earlier. There is one big reason for this, earlier the size of data-set was small so it could fit into memory. Now that the data-set has grown large enough that it cannot fit entirely into memory, the disk seeks really have slowed down the queries significantly. What to do now? Vertical partitioning. Divide the data-set into separate data-sets vertically....
    Keeping save – Injection attacks etc.
    +1 Vote Up -0Vote Down

    I had someone point out to me that some of the sample code I have used might be vulnerable to an insertion attack. So I feel it is time to address this subject.

    First when using examples I try to just focus on the subject of the current blog and often strip a lot of things that are irrelevant to the point I am trying to make out. But now to the subject of keeping your code save.

    If you write and SQL statement looking something like

    lc_sql = “SELECT * from customer WHERE custno = ‘” + lc_custno + “‘”

    Someone could enter “‘; TRUNCATE TABLE customer;’” resulting in  a SQL statement looking like

    SELECT * from customer WHERE custno = ”; TRUNCATE TABLE customer; ”

    Which could be quite disasterous. So to avoid this we could use parameterized queries which I cannot as I am using a seperate function to

      [Read more...]
    Converting and optimizing SQL Statements
    +0 Vote Up -0Vote Down

    Now it became time to conveert some SQL statements. Even though many SELECT SQL statements will run unchanged in MYSQL it might be wise to check and see if they are performing well. There are some differences in how VFP and MYSQL optimize queries and a query that just performs great in VFP might be very slow in MYSQL. And then there are some cases where we have to replace things that are possible in VFP but not supported in MYSQL.

    First lets assume that our tables all have lots of records. After all it does not pay to spend time on optimizing on tables with a few dozend records. I also will not go into VFP optimization here as this is a MYSQL blog.

    Lets assume for our first case we might have a table with invoice detail records and a table with items that are on sale. So now we want to get maybe 2 queries. One to show a total of all the items that were on sale and the second a total of all the

      [Read more...]
    Showing entries 1 to 10 of 13 3 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.