making query faster

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

making query faster

Post 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?
velo
Forum Newbie
Posts: 7
Joined: Sun May 14, 2006 2:42 pm

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post 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...
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

do you mind posting the solution? :)
Post Reply