Page 1 of 1
making query faster
Posted: Sun May 14, 2006 3:10 pm
by newmember
hi,
here is a typical query:
table t has two numeric columns a1 and a2.
both columns indexed
Code: Select all
$n=400;
"SELECT * FROM t WHERE ".$n."<a2 AND ".$n.">a1";
the problem is that only one index is used here iether on a1 or a2 and table scans are unavoidable...
the only idea i had is to use spacial data types,treating a1 and a2 columns as intervals,
but maybe someone can offer something else?
Posted: Tue May 16, 2006 7:32 am
by velo
There really doesn't seem to be any way to speed this up unless you could somehow group similar values (for columns a1 and a2) within the database so that the query would not search the entire table/db.
Your query has to do two comparisons to see if the record should be returned and so each of those values must be read and compared to the value of $n.
Posted: Tue May 16, 2006 2:00 pm
by raghavan20
you can create an index on two columns and use explain to see how it uses indices.
Code: Select all
mysql> create table numbers(
-> n1 int,
-> n2 int);
Query OK, 0 rows affected (0.25 sec)
mysql> insert into numbers values (1, 3), (2, 4), (5, 6);
Query OK, 3 rows affected (0.36 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from numbers;
+------+------+
| n1 | n2 |
+------+------+
| 1 | 3 |
| 2 | 4 |
| 5 | 6 |
+------+------+
3 rows in set (0.00 sec)
without any index:
mysql> select * from numbers where n1=5 and n2=6;
+------+------+
| n1 | n2 |
+------+------+
| 5 | 6 |
+------+------+
1 row in set (0.00 sec)
with n1 index:
mysql> alter table numbers add index n1 (n1);
Query OK, 3 rows affected (0.24 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from numbers where n1=5 and n2=6;
+------+------+
| n1 | n2 |
+------+------+
| 5 | 6 |
+------+------+
1 row in set (0.01 sec)
it is a bit longer bcos it is referencing indexes, but this would be opposite when you have many rows in a table
mysql> explain select * from numbers where n1=5 and n2=6;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | numbers | ref | n1 | n1 | 5 | const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> alter table numbers add index n2 (n2);
Query OK, 3 rows affected (0.61 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from numbers where n1=5 and n2=6;
+------+------+
| n1 | n2 |
+------+------+
| 5 | 6 |
+------+------+
1 row in set (0.00 sec)
mysql> explain select * from numbers where n1=5 and n2=6;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | numbers | ref | n1,n2 | n1 | 5 | const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
not it uses n1 and n2 but still not good enough
mysql> alter table numbers add index n1_n2 (n1, n2);
Query OK, 3 rows affected (0.56 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from numbers where n1=5 and n2=6;
+------+------+
| n1 | n2 |
+------+------+
| 5 | 6 |
+------+------+
1 row in set (0.02 sec)
mysql> explain select * from numbers where n1=5 and n2=6;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | numbers | ref | n1,n2,n1_n2 | n1 | 5 | const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
now it will use n1_n2 to find the combination
try this on a large table to see the actual timings.
Posted: Tue May 16, 2006 3:20 pm
by newmember
thanks for replies
i got an answer on another forum and it is to use compound index... so that db will use only index and doesn't have to go to the table...
Posted: Tue May 16, 2006 4:32 pm
by raghavan20
do you mind posting the solution?
