We use a sub select on a 8M+ row table because it takes better advantage
of indexes.
SELECT startip,endip FROM geodb a
WHERE a.startip = (SELECT max(startip) FROM geodb WHERE b.startip <=
3250648033) AND a.endip >= 3250648033;
startip and endip are INT(10) unsigned and unique keys.
This returns, on a fairly crappy old system in milliseconds after the
table is loaded.
Carlo, What do your tables look like exactly, and what are you
considering to be poor performance?
Look up the profiling flag, if you set that, you can get a detailed
breakdown on the time spent in each query.
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from visitor;
+----------+
| count(*) |
+----------+
| 152 |
+----------+
1 row in set (0.00 sec)
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| (initialization) | 0.000008 |
| checking query cache for query | 0.000232 |
| Opening tables | 0.000048 |
| System lock | 0.000025 |
| Table lock | 0.000125 |
| init | 0.000062 |
| optimizing | 0.000034 |
| executing | 0.000314 |
| end | 0.000019 |
| query end | 0.000012 |
| storing result in query cache | 0.000245 |
| freeing items | 0.00003 |
| closing tables | 0.000023 |
| logging slow query | 0.000011 |
+--------------------------------+----------+
14 rows in set (0.01 sec)
http://www.futhark.ch/mysql/122.html is a good tut on joining a table on
itself which might be where you are going. Don't use cross joins. Just
do some googling as to why.
Thanks,
Eric
Ananda Kumar wrote:
in mysql sub queries dont perform well.
You can could try this
SELECT a.ID
FROM ven_tes a, ven_tes b where a.id=b.id and b.id_ven=6573 .
On 5/20/08, Wakan <[EMAIL PROTECTED]> wrote:
Hi,
can someone could explain where are problems in this query:
EXPLAIN
SELECT ID
FROM ven_tes
WHERE ID IN (SELECT ID FROM ven_tes WHERE ID_ven=6573)
+----+--------------------+---------+-----------------+----------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+--------------------+---------+-----------------+----------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | ven_tes | index | NULL |
PRIMARY | 4 | NULL | 6573 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | ven_tes | unique_subquery | PRIMARY,ID_ven |
PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+---------+-----------------+----------------+---------+---------+------+------+--------------------------+
as you can see, it doesn't use absolutely indexes on ven_tes (ID is the
primary key, ID_ven is index)
Thanks in advance
Carlo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
