Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

I'm converting a PHP app from using Visual FoxPro as the database
backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on
Mac OSX 10.4. The end application will be deployed cross platform and
to both 4.x and 5.x MySQL servers.

This query returned 21 records in .27 seconds.

SELECT zip FROM zipcodes WHERE
degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
< 5

This query returned 21442 records in 1.08 seconds.

SELECT custzip FROM customers

This query is still running half an hour later, with a Time of 2167
and a State of "Sending Data" (according to the mysql process list)

SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
< 5)

When I try to EXPLAIN the query it gives me the following...

id,select_type,table,type,possible_keys,key,key_len,ref,rows ,Extra
1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Usin g where; Using index
2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where

If it matters both tables are INNODB and both customers.custzip and
zipcodes.zip are indexed. We used a program called DBConvert from
DMSoft to convert the data so it's "exactly" the same on both the VFP
side and the MySQL side. With all that in mind... VFP returns the
exact same query in 5-10 seconds and that includes render time in the
web browser.

By comparison... the query WHERE id IN (SELECT id FROM phone WHERE
phonedate >= '2001-01-01' AND phonedate <= '2009-06-18') returns
almost instantly.

I'm at a complete loss... The suggestions I've seen online for
optimizing Dependent Subquery's basically revolve around changing it
from a sub-query to a join but that would require more
re-architecturing than I want to do... (Unless I'm forced) Especially
since more than a few of those solutions suggested precalculating the
distance between zipcodes which only works if the distances are known
(only allow 10, 50 and 100 mile radi for example)

Any ideas?

Thanks in advance!

Matt

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

--0016e64c1ad01a61bf046caa49e7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I often find doing the IN (subquery...) is really slow versus doing a join:

SELECT cutzip
FROM customers
INNER JOIN zipcodes ON customers.zipcode=zipcodes.zip
WHERE
degrees(acos(sin(radians(39.0788994))
*
sin(radians(latitude))
+
cos(radians(39.0788994))
*
cos(radians(latitude))
*
cos(radians(-77.1227036-longitude))
)
)
*60
*1.1515
< 5

That query may have un-matched ()'s, not sure. hard to tell =)

Try a join.

-jw

On Thu, Jun 18, 2009 at 8:06 PM, Matt Neimeyer wrote:

> I'm converting a PHP app from using Visual FoxPro as the database
> backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on
> Mac OSX 10.4. The end application will be deployed cross platform and
> to both 4.x and 5.x MySQL servers.
>
> This query returned 21 records in .27 seconds.
>
> SELECT zip FROM zipcodes WHERE
> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
>
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
> < 5
>
> This query returned 21442 records in 1.08 seconds.
>
> SELECT custzip FROM customers
>
> This query is still running half an hour later, with a Time of 2167
> and a State of "Sending Data" (according to the mysql process list)
>
> SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
> zipcodes WHERE
> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
>
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
> < 5)
>
> When I try to EXPLAIN the query it gives me the following...
>
> id,select_type,table,type,possible_keys,key,key_len,ref,rows ,Extra
> 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Usin g where; Using
> index
> 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where
>
> If it matters both tables are INNODB and both customers.custzip and
> zipcodes.zip are indexed. We used a program called DBConvert from
> DMSoft to convert the data so it's "exactly" the same on both the VFP
> side and the MySQL side. With all that in mind... VFP returns the
> exact same query in 5-10 seconds and that includes render time in the
> web browser.
>
> By comparison... the query WHERE id IN (SELECT id FROM phone WHERE
> phonedate >= '2001-01-01' AND phonedate <= '2009-06-18') returns
> almost instantly.
>
> I'm at a complete loss... The suggestions I've seen online for
> optimizing Dependent Subquery's basically revolve around changing it
> from a sub-query to a join but that would require more
> re-architecturing than I want to do... (Unless I'm forced) Especially
> since more than a few of those solutions suggested precalculating the
> distance between zipcodes which only works if the distances are known
> (only allow 10, 50 and 100 mile radi for example)
>
> Any ideas?
>
> Thanks in advance!
>
> Matt
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e64c1ad01a61bf046caa49e7--

How to Optimize distinct with index

Hi,
I have a sql :
Select distinct user_id from user where key1=value and
key2=value2 and key3=value2;

I add index on (key1,key2,key3,user_id), this sql use temporary table
howevery
I have thousands of queries per second.
How to optimize it?


Anthoer question:
Select * from user where user_id in(id1,id2,id3,id4,.....) order by use_id;
I add index on user_id,but after in,order use temporary table, How to
optimize it?

Thanks!

zhouyanwei



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

--------------090007030200030806060205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matt,

>This query is still running half an hour later, with a Time of 2167
>and a State of "Sending Data" (according to the mysql process list)
> SELECT custzip FROM customers WHERE custzip IN ( ...

For explanation & alternatives see "The unbearable slowness of IN()" at
http://localhost/artful/infotree/queries.php.

PB

-----

Matt Neimeyer wrote:
> I'm converting a PHP app from using Visual FoxPro as the database
> backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on
> Mac OSX 10.4. The end application will be deployed cross platform and
> to both 4.x and 5.x MySQL servers.
>
> This query returned 21 records in .27 seconds.
>
> SELECT zip FROM zipcodes WHERE
> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
> < 5
>
> This query returned 21442 records in 1.08 seconds.
>
> SELECT custzip FROM customers
>
> This query is still running half an hour later, with a Time of 2167
> and a State of "Sending Data" (according to the mysql process list)
>
> SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
> zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
> < 5)
>
> When I try to EXPLAIN the query it gives me the following...
>
> id,select_type,table,type,possible_keys,key,key_len,ref,rows ,Extra
> 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Usin g where; Using index
> 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where
>
> If it matters both tables are INNODB and both customers.custzip and
> zipcodes.zip are indexed. We used a program called DBConvert from
> DMSoft to convert the data so it's "exactly" the same on both the VFP
> side and the MySQL side. With all that in mind... VFP returns the
> exact same query in 5-10 seconds and that includes render time in the
> web browser.
>
> By comparison... the query WHERE id IN (SELECT id FROM phone WHERE
> phonedate >= '2001-01-01' AND phonedate <= '2009-06-18') returns
> almost instantly.
>
> I'm at a complete loss... The suggestions I've seen online for
> optimizing Dependent Subquery's basically revolve around changing it
> from a sub-query to a join but that would require more
> re-architecturing than I want to do... (Unless I'm forced) Especially
> since more than a few of those solutions suggested precalculating the
> distance between zipcodes which only works if the distances are known
> (only allow 10, 50 and 100 mile radi for example)
>
> Any ideas?
>
> Thanks in advance!
>
> Matt
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.364 / Virus Database: 270.12.78/2185 - Release Date: 06/18/09 05:53:00
>
>

--------------090007030200030806060205--

Re: How to Optimize distinct with index

Please don't change the subject on someone else's thread. Next time, post a
new message instead of hitting "reply" on an unrelated message.

In the last episode (Jun 19):
> Hi,
> I have a sql :
> Select distinct user_id from user where key1=value and
> key2=value2 and key3=value2;
>
> I add index on (key1,key2,key3,user_id), this sql use temporary table
> however. I have thousands of queries per second. How to optimize it?

Because of the "distinct" clause, mysql has to remember all of the "user_id"
values during the query so it can remove duplicates. You do have an index
containing all of your fields, which certainly helps performance, but
because the index is sorted with user_id last, mysql can't use that index to
perform the "distinct" operation. Imagine your query returns 1000 rows with
998 unique usernames that happen to have key1 values from 2..999, and one
duplicate username that happens to have rows with key1=1 and key1=1000.
Because it's using the index to fetch data, rows will be sorted by key1, and
the duplicate name will be in the first and last rows. Mysql needs to store
the names in a temporary table to be able to remove the duplicates.

Now, if user_id were first, mysql could use it directly to remove duplicates
(since it would see duplicate names next to each other), but it wouldn't be
able to use that index in your where clause.. :( You can't win in this
case.

Luckily, temporary tables aren't bad as long as they are small and mysql
doesn't have to write them to disk. To be sure, run "show status like
'created_%'" before and after a query and see if the Created_tmp_disk_tables
number increases. As long as the temp tables stay in RAM, your query will
be efficient.

http://dev.mysql.com/doc/refman/5.1/en/server-status-variabl es.html#statvar_Created_tmp_disk_tables

> Anthoer question:
>
> Select * from user where user_id in(id1,id2,id3,id4,.....) order by
> user_id; I add index on user_id, but after in, order use temporary table,
> How to optimize it?

Mysql should have been able to use the index here, I think. Please post the
output of "create table user", a sample query, and its EXPLAIN output.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

In the last episode (Jun 18), Matt Neimeyer said:
> I'm converting a PHP app from using Visual FoxPro as the database backend
> to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX
> 10.4. The end application will be deployed cross platform and to both 4.x
> and 5.x MySQL servers.
>
> This query returned 21 records in .27 seconds:
>
> SELECT zip FROM zipcodes WHERE
> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
> < 5

Ouch. You might want to calculate the rectange enclosing your target
distance, add an index on lat (and/or long), and add the rectangle check to
your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
ln2 AND ln2. That way mysql can use the index to pare down most of the rows
without having to call all those trig functions for every zipcode.

> This query returned 21442 records in 1.08 seconds:
>
> SELECT custzip FROM customers
>
> This query is still running half an hour later, with a Time of 2167
> and a State of "Sending Data" (according to the mysql process list)
>
> SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
> zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
> < 5)
>
> When I try to EXPLAIN the query it gives me the following...
>
> id,select_type,table,type,possible_keys,key,key_len,ref,rows ,Extra
> 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Usin g where; Using index
> 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where

Neither mysql 4 or 5 are very smart when it comes to subqueries. Unless
your inner query is dead simple, mysql assumes it's a dependent subquery and
runs it once per row in your outer query. You might want to try mysql 6 and
see if it does any better. For example, here are explain plans for mysql 5
and 6 for the following query on the famous Oracle "emp" sample table:

select ename from emp where mgr in
(select empno from emp where ename in ('scott'));
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)

Mysql 5.1.30:
+----+--------------------+-------+-----------------+------- --------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+------- --------+---------+---------+------+------+-------------+
| 1 | PRIMARY | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
| 2 | DEPENDENT SUBQUERY | emp | unique_subquery | PRIMARY,ENAME | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-------+-----------------+------- --------+---------+---------+------+------+-------------+

Note that it didn't use an index on the outer query, and had to examine all
14 rows. It even used the wrong index on the inner query :)

Mysql 6.0.11:
+----+-------------+-------+------+---------------+-------+- --------+----------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+- --------+----------------+------+-----------------------+
| 1 | PRIMARY | emp | ref | PRIMARY,ENAME | ENAME | 13 | const | 1 | Using index condition |
| 1 | PRIMARY | emp | ref | MGR | MGR | 5 | test.emp.EMPNO | 2 | |
+----+-------------+-------+------+---------------+-------+- --------+----------------+------+-----------------------+

Note that the queries have flipped and aren't nested anymore (id is 1 on
both queries). The first query uses the ename index and estimates it will
return one row. The second query uses the mgr index based on the empno
value returned by the first query and estimates it will return 2 rows. Much
better :)

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to Optimize distinct with index

Select user_id from user where key1=3Dvalue and
key2=3Dvalue2 and key3=3Dvalue2 GROUP BY user_id

is faster than


Select distinct user_id from user where key1=3Dvalue and
key2=3Dvalue2 and key3=3Dvalue2;


2009/6/18 ÖÜÑåΰ :
> Hi,
> I have a sql :
> Select distinct user_id from user where key1=3Dvalue and
> key2=3Dvalue2 and key3=3Dvalue2;
>
> I add index on (key1,key2,key3,user_id), this sql use temporary table
> howevery
> I have thousands of queries per second.
> How to optimize it?
>
>
> Anthoer question:
> Select * from user where user_id in(id1,id2,id3,id4,.....) order by use_i=
d;
> I add index on user_id,but after in,order use temporary table, How to
> optimize it?
>
> Thanks!
>
> zhouyanwei
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Ddsteplight@gmail.=
com
>
>



--=20
A: It reverses the normal flow of conversation.
Q: What's wrong with top-posting?
A: Top-posting.
Q: What's the biggest scourge on plain text email discussions?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

>> SELECT zip FROM zipcodes WHERE
>> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
>> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-=
longitude))))*60*1.1515
>> < 5
>
> Ouch. =A0You might want to calculate the rectange enclosing your target
> distance, add an index on lat (and/or long), and add the rectangle check =
to
> your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWE=
EN
> ln2 AND ln2. =A0That way mysql can use the index to pare down most of the=
rows
> without having to call all those trig functions for every zipcode.

I like this idea the best (it always bothered me running a query that
involved multiple mathmatical functions).

So... Here's the "scratch" php code I ended up with... Anyone see any
problems with it? The only problem I see is that I think the old code
was more "circular" this will be a square (within the limits of a
square on a non-spherical earth... etc.. etc..) ... so there will be
more zip codes included in the corners. If there are too many
complaints about that I might look at some sort of overlapping
rectangle scheme instead of a square.

function ChangeInLatitude($Miles) { return rad2deg($Miles/3960); }
function ChangeInLongitude($Lat, $Miles) { return
rad2deg($Miles/3960*cos(deg2rad($Lat))); }

$Miles =3D 5;

$OriginalLat =3D 39.0788994;
$OriginalLon =3D -77.1227036;

$ChangeInLat =3D ChangeInLatitude($Miles);
$ChangeInLon =3D ChangeInLongitude($OriginalLat, $Miles);

$MinLat =3D $OriginalLat-$ChangeInLat;
$MaxLat =3D $OriginalLat+$ChangeInLat;

$MinLon =3D $OriginalLon-$ChangeInLon;
$MaxLon =3D $OriginalLon+$ChangeInLon;

My only other question is... when I explained the new query... On the
dependent subquery it says possible keys are zip, longitude and
latitude but it used zip. It seems like a better index would be
longitude or latitude? On the primary query, even though there is an
index on custzip it doesn't say it's using ANY indexes. I should
probably leave well enough alone... but I'm curious.

Thanks again!

Matt

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

It sounds like you want to use spatial indexes, but they only became
available in v4.1
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index .html

You would need to switch your table type from InnoDB to MyISAM, which
is fairly easy with ALTER TABLE. But that should allow you to drop all
your calculations in the query.

You don't have to do any re-architecture to change you subquery to a join:
SELECT custzip FROM customers
JOIN
(SELECT zip FROM
zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) =
+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-lon=
gitude))))*60*1.1515
< 5) AS zips
ON custzip=3Dzip

Often times that simple change speeds things up considerably in MySQL.
An explain should show it has a DERIVED TABLE if I recall correctly.

Brent Baisley

On Thu, Jun 18, 2009 at 9:06 PM, Matt Neimeyer wrote:
> I'm converting a PHP app from using Visual FoxPro as the database
> backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on
> Mac OSX 10.4. The end application will be deployed cross platform and
> to both 4.x and 5.x MySQL servers.
>
> This query returned 21 records in .27 seconds.
>
> =A0 SELECT zip FROM zipcodes WHERE
> degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-l=
ongitude))))*60*1.1515
> < 5
>
> This query returned 21442 records in 1.08 seconds.
>
> =A0 SELECT custzip FROM customers
>
> This query is still running half an hour later, with a Time of 2167
> and a State of "Sending Data" (according to the mysql process list)
>
> =A0 SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
> zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude=
))+
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-l=
ongitude))))*60*1.1515
> < 5)
>
> When I try to EXPLAIN the query it gives me the following...
>
> id,select_type,table,type,possible_keys,key,key_len,ref,rows ,Extra
> 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Usin g where; Usin=
g index
> 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where
>
> If it matters both tables are INNODB and both customers.custzip and
> zipcodes.zip are indexed. We used a program called DBConvert from
> DMSoft to convert the data so it's "exactly" the same on both the VFP
> side and the MySQL side. With all that in mind... VFP returns the
> exact same query in 5-10 seconds and that includes render time in the
> web browser.
>
> By comparison... the query WHERE id IN (SELECT id FROM phone WHERE
> phonedate >=3D '2001-01-01' AND phonedate <=3D '2009-06-18') returns
> almost instantly.
>
> I'm at a complete loss... The suggestions I've seen online for
> optimizing Dependent Subquery's basically revolve around changing it
> from a sub-query to a join but that would require more
> re-architecturing than I want to do... (Unless I'm forced) Especially
> since more than a few of those solutions suggested precalculating the
> distance between zipcodes which only works if the distances are known
> (only allow 10, 50 and 100 mile radi for example)
>
> Any ideas?
>
> Thanks in advance!
>
> Matt
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dbrenttech@gma=
il.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

Peter,


On Thu, Jun 18, 2009 at 9:27 PM, Peter
Brawley wrote:
> For explanation & alternatives see "The unbearable slowness of IN()" at
> http://localhost/artful/infotree/queries.php.
>
you prolly meant to not post a url pointing at your local copy of your
website. This works better for most of us:
http://www.artfulsoftware.com/infotree/queries.php ;)

Walter



--
Walter Heck, Engineer @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL & related technologies

Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?

On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisley wrote:
> It sounds like you want to use spatial indexes, but they only became
> available in v4.1
> http://dev.mysql.com/doc/refman/5.0/en/create-index.html
> http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index .html

That "feels" like the right thing (spatial calculations = spatial
indexes?) but I looked at the docs and my head exploded. Can anyone
recommend a good book that takes me through it gently?

That said I'm intreged by the MBRContains and the Polygon functions...
If I read those right I could create a simplified "circle" (probably
just an octogon) to help eliminate false positives in the "corners"
when using a plain square as the enclosure.

> You don't have to do any re-architecture to change you subquery to a join:
> SELECT custzip FROM customers
> JOIN
> (SELECT zip FROM
> zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude)) +
> cos(radians(39.0788994))*cos(radians(latitude))*cos(radians( -77.1227036-longitude))))*60*1.1515
> < 5) AS zips
> ON custzip=zip

Will that work after a where clause? Multiple Times? For example...
(pseudo-code...)

SELECT * FROM customers WHERE saleslastyear > 100000
JOIN (SELECT zip FROM etc....) AS zips ON custzip=zip
JOIN (SELECT MAX(date) FROM phonecalls) AS LastCalledOn ON custid=custid

Just from thinking about that... I assume that the only limitation is
that in a subselect you can do something like WHERE NOT IN (select
etc) but with a JOIN you are assuming a "positive" relationship? For
example using the JOIN methods above there isn't a way to simply do
"AND custid NOT IN (SELECT custid FROM ordersplacedthisyear)" other
than doing exactly that and adding this clause to the saleslastyear
clause. (In this particular case a column "lastorderdate" in customer
that was programatically updated on ordering would also be useful but
I'm thinking examples here... ;) )

I've never seen JOIN used outside of a traditional "SELECT t1.*,t2.*
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id=t2.id" type of
structure so I kinda feel like I have a new toy...

Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to Optimize distinct with index

--0016e64c0a867b0d35046d3c1733
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

Hi.
I think you should create an index like this.
alter table user add index idx_tmp (key1,key2,key3,user_id)

2009/6/19 Darryle Steplight

> Select user_id from user where key1=3Dvalue and
> key2=3Dvalue2 and key3=3Dvalue2 GROUP BY user_id
>
> is faster than
>
>
> Select distinct user_id from user where key1=3Dvalue and
> key2=3Dvalue2 and key3=3Dvalue2;
>
>
> 2009/6/18 ÖÜÑåΰ :
> > Hi,
> > I have a sql :
> > Select distinct user_id from user where key1=3Dvalue and
> > key2=3Dvalue2 and key3=3Dvalue2;
> >
> > I add index on (key1,key2,key3,user_id), this sql use temporary table
> > howevery
> > I have thousands of queries per second.
> > How to optimize it?
> >
> >
> > Anthoer question:
> > Select * from user where user_id in(id1,id2,id3,id4,.....) order by
> use_id;
> > I add index on user_id,but after in,order use temporary table, How to
> > optimize it?
> >
> > Thanks!
> >
> > zhouyanwei
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Ddsteplight@gmail.com
> >
> >
>
>
>
> --
> A: It reverses the normal flow of conversation.
> Q: What's wrong with top-posting?
> A: Top-posting.
> Q: What's the biggest scourge on plain text email discussions?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608@gmail.c om
>
>


--=20
David Yeung,
MySQL Senior Support Engineer,
Sun Gold Partner.
My Blog:http://yueliangdao0608.cublog.cn

--0016e64c0a867b0d35046d3c1733--