explain

Why the rows returns by "explain" is not equal to count()?

mysql> select count(*) from table where relation_title='xxxxxxxxx'; +----------+ | count(*) | +----------+ | 1291958 | +----------+ mysql> explain select * from table where relation_title='xxxxxxxxx'; +----+-------------+---------+- | id | select_type | rows | +----+-------------+---------+- | 1 | SIMPLE | 1274785 | +---...

How do I use DB2 Explain?

How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2? I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them -- which is hardly ideal. Edit: The answer for me turned out to be "You can't. You don't ha...

Optimizing MySQL Queries: Is it always possible to optimize a query so that it doesn't use "ALL"

According to the MySQL documentation regarding Optimizing Queries With Explain: * ALL: A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that ...

Slow MySQL Query

Hi, I have a query in MySQL (used in a stored procedure) which searches by name and another field. When I use different combinations of these search parameters, I get quick results (between 1 and 2s) but with some particular values, I get a query which takes 9s to return results on the production website. The following is what I got out...

How to optimize my SQL in server?

I would like to know how to optimize the following SQL to let my server load faster and take low usage? I need to calculate the radius distance for a US ZIP Code to get the result, such as 50 miles from a particular ZIP Code ( using latitude and longitude to calculate ) and to getting how many other data ( e.g. others ZIP Code ) from my...

How does space partitioning algorithm for finding nearest neighbors work?

For finding the nearest neighbor, Space Partitioning is one of the algorithms. How does it work? Suppose I have a 2D set of points (x and y coordinates), and I am given a point (a,b). How would this algorithm find out the nearest neighbor? ...

MySql and inline SELECTs

I have a query that looks like this: select id , int1 , int2 , (select count(*) from big_table_with_millions_of_rows where id between t.int1 and t.int2) from myTable t where .... This select returns exactly one row. The id used in the inline select is an indexed column (primary key). If I replace t.int1 and t.int2 with the values...

Mysql Query optimisation

Query 1: SELECT cid, dl FROM chal WHERE cid IN ( SELECT cid FROM c_users WHERE uid = 636587 ); Query 2: SELECT chal.cid AS cid, chal.dl AS dl FROM chal, c_users WHERE uid = 808 AND chal.cid = c_users.cid; cid is primary key in chal cid and uid are...

sqlite explain from api

I have a complicated SQL query executed from my application. The query runs fine with the version 3.6.11 (64ms). When run in 3.6.22 it takes more than 100 sec to finish. When executed from the command line client, the execution is quick both in 11 and 22. Therefore I want to run the query with "EXPLAIN" from inside my application. Is it ...

The explain tells that the query is awful (it doesn't use a single key) but I'm using LIMIT 1. Is this a problem?

The explain command with the query: explain SELECT * FROM leituras WHERE categorias_id=75 AND textos_id=190304 AND cookie='3f203349ce5ad3c67770ebc882927646' AND endereco_ip='127.0.0.1' LIMIT 1 The result: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE ...

Can someone explain/annotate this Ruby snippet with comments?

Could someone please add comments to this code? Or, alternatively, what would be the pseudocode equivalent of this Ruby code? It seems simple enough but I just don't know enough Ruby to convert this to PHP. data = Hash.new({}) mysql_results.each { |r| data[r['year']][r['week']] = r['count'] } (year_low..year_high).each do |year| (1....

Explaining your system to a client

I'm currently developing a small Database Management System for a local company. How would you go about explaining how the system you have designed to a client? If they are non-technical and have no understanding of programming, how would you go about showing what the system will do and how it will do it? I guess some sort of visual repr...

What is the explanation of this java code ?

I have the following code : public class Main { public void method(Object o) { System.out.println("Object Version"); } public void method(String s) { System.out.println("String Version"); } public static void main(String args[]) { Main question = new Main(); ...

How to optimize this mysql query - explain output included

This is the query (a search query basically, based on tags):- select SUM(DISTINCT(ttagrels.id_tag in (2105,2120,2151,2026,2046) )) as key_1_total_matches, td.*, u.* from Tutors_Tag_Relations AS ttagrels Join Tutor_Details AS td ON td.id_tutor = ttagrels.id_tutor JOIN Users as u on u.id_user = td.id_user where (ttagrels.id_tag in (210...

PostgreSQL: exists vs left join

I heard many times that postgres handles exists queries even faster then left join. http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php That's definitely true for one table aggregation. But in our case their is more then one and the same query build with exists that make postgres to hang forever: explain SELECT coun...

In `equals(T value)`, must T be Object, or can it be like City, etc?

I'm trying to understand the equals() method better. All examples I've seen do something like: public class City { public boolean equals(Object other) { if (other instanceof City && other.getId().equals(this.id)) { return true; } // ... } } Must the method take on Object and not...

What's a buffer?

Hello! As far as my understanding of languages goes, a buffer is any portion of memory in which a data is stored like an int,float variables, character arrays etc. However, I was reading buffer overflows and came across this link while reading about stack http://www.tenouk.com/Bufferoverflowc/Bufferoverflow2a.html The diagram in this li...

PHP Plugins System

Possible Duplicate: Best way to allow plugins for a PHP application Hello all.. I'd like to learn how does the php plugins system works.. So can anyone explain the idea of it for me in detail, I saw many posts and explanations but I didn't learn and understand any of it. Edited: Sorry.. I meant if I want to make a plugins sy...

mysql three joins

Hi, I have a problem with mysql I have 3 tables: Deposit +-------------------+-------------+------+-----+ | Field | Type | Null | Key | +-------------------+-------------+------+-----+ | id | bigint(20) | NO | PRI | | status | int(2) | NO | | | depositDate | datetime |...

what is the Message Exchange Patterns, in plain english?

Can someone please explain in plain English what the MEP is? I can't grasp the concept. From what I figure, is just a concept for two parties to understand each other (i.e. agree on a type of format that the messages they exchange between them must have). Is this it, or is there more? Thank you! ...