I'm trying to understand why MySQL isn't using all parts of the primary
key when I add in an OR statement in the query.
I have a people database, and a distances database. The people database
lists a person's name and their location, the distances database
contains the distance between any two points in the country. There are
an roughly 15000^2 entries in the distances table, and currently about
100 entries in the people table. The schemas are as follows:
table distances
(
sourceId smallint unsigned not null,
destinationId smallint unsigned not null,
distance smallint unsigned not null, -- distance in kilometres
primary key(sourceId, destinationId)
) ENGINE = MYISAM ROW_FORMAT = FIXED PACK_KEYS = 1;
table people
(
personId int unsigned not null auto_increment primary key,
personName varchar(50) not null
) ENGINE = MYISAM;
The following queries aim at finding all people located within some
user specified radius centred around some user specified point. The
user has the option of specifying multiple points, eg: find me all
people located within (100 kms of point
A) or (300kms of point B).
In the next query, with the explain output attached, the user has
entered one centre point which he wishes to search around.
explain select * from people,distance s as d1 where
people.location Id = d1.sourceId and d1.destinationI d = 2 and
d1.distance <= 1000
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | people | ALL | sourceId | NULL | NULL | NULL | 103 | |
| 1 | SIMPLE | d1 | eq_ref | PRIMARY | PRIMARY | 4 |
db.people.locat ionId,const | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
SQL is using both parts of the key, as expected.
However, when the user specifies two centre points he wishes to search
around:
explain select * from people,distance s as d1 where
people.location Id = d1.destinationI d and d1.sourceId = 2 and
d1.distance <= 1000
or
people.location Id = d1.destinationI d and d1.sourceId = 3 and
d1.distance <= 2000
+----+-------------+-------+-------+---------------+----------+---------+-------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------------------------+-------+-------------+
| 1 | SIMPLE | d1 | range | PRIMARY | PRIMARY | 2 | NULL | 30731 |
Using where |
| 1 | SIMPLE | people | ref | sourceId | sourceId | 2 |
db.d1.destinati onId | 11 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+-------------------------+-------+-------------+
The optimiser chooses to join people to d1. It can't use the
people.location Id constraint in scanning the d1 table, so it only uses
the constant constraint. (Side note: why does the 'ref' column read
'NULL' - isn't mysql using the constants
2 & 3 and matching them to the key?)
In an effort to get mysql to reverse the join order (and thus make it
possible to use both parts of the keys), I used straight_join:
explain select straight_join * from people,distance s as d1 where
people.location Id = d1.destinationI d and d1.sourceId = 2 and
d1.distance <= 1000
or
people.location Id = d1.destinationI d and d1.sourceId = 3 and
d1.distance <= 2000
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | people | ALL | sourceId | NULL | NULL | NULL | 101 | |
| 1 | SIMPLE | d1 | range | PRIMARY | PRIMARY | 2 | NULL | 30731 |
Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
Still mysql is only using part of the key (I assume the d1.sourceId
part of the key), where I think it could use
both parts of the key, and reduce the number of rows down to 2, so that
mysql refers to both the constant and the
db.people.locat ionId constraints, as in the ver first query. I think a
possible problem is that mysql doesn't know
that each OR statement defines exactly one entry in the distance table,
but I'm just guessing.
Any ideas?
Thanks
Taras 0 1342 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Aditya Ivaturi |
last post by:
We have a CMS which is written is based on php & mysql. Recently we received
a request to support multiple languages so that sites in that particular
laguage can be created. I did some search on the google and it seems I have
to build in multibyte support for php and mysql. Mbstring
(http://us3.php.net/mbstring) claims to support multiple languages with a
caution saying it might not work properly.
After further research it seems unicode...
|
by: Mr Dygi |
last post by:
Hi,
PHP 4.3.4 installed manually from package *.zip and Apache 2. I have a
problem with this simple code:
<?php
$link = mysql_connect("127.0.0.1","","")
or die("Could not connect: " . mysql_error());
echo "Connected successfully";
mysql_close($link);
|
by: Stefan Hinz |
last post by:
Degan,
jumping in to try and solve some problems that look pretty obvious to
me ...
> #options for default service (mysqld2)
> (mysqld2)
It should be , not (mysqld2).
|
by: B. Pigman |
last post by:
There have been many questions as to the viability of MySQL's
assertion that it can dictate what constitutes a derived work in order
to use the GPL against developers who don't wish their software GPL'd
and force them to pay for a commercial license.
According to the lawyers I've consulted, based on the letter of the
GPL, here is the conclusion:
Commercial users of MySQL opting for the GPL'd version are not
compelled to release their...
|
by: Fred |
last post by:
Hello,
Our website is currently developed in ASP/Mysql 4.
The dedicated servers on which it is currently hosted arrive at saturation.
Here is their configuration:
- 1 server PIV 2,8Ghz 1GB RAM with IIS 5 on Windows 2000
- 1 server Bi-xeon 3Ghz, 512 MB with MySQL 4 on Windows 2003
The website makes approximately 10.000.000 of pages seen and 310.000
| |
by: Jim Hubbard |
last post by:
I have a client that desires a new application for use at several stores in
different cities. He wants "real time" access to all store data and the
ability to continue to run locally even if the internet connection goes
down.
It occurs to me that a local MySQL instance should definitely be
in each store to continue work if the net is down for any reason. But, what
is the best way to synchronize/share data among all of the stores?
...
|
by: alf |
last post by:
Hi,
is it possible that due to OS crash or mysql itself crash or some e.g.
SCSI failure to lose all the data stored in the table (let's say million
of 1KB rows). In other words what is the worst case scenario for MyISAM
backend?
Also is it possible to not to lose data but get them corrupted?
|
by: Ted |
last post by:
In MS SQL I used the following to create a stored procedure.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_My_Search', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.usp_My_Search;
GO
CREATE PROCEDURE HumanResources.usp_My_Search
@searchstring varchar(40)
AS
|
by: Atli |
last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users.
Anyone should be able to get through this without much trouble. Programming knowledge is not required.
Index
What is SQL?
Why MySQL?
Installing MySQL.
Using the MySQL command line interface
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |