CODEWITHSUNDEEP
mysqlindexingdatabase-performance
NovumCoder
NovumCoder

Reputation: 4647

mysql does not use primary bigint index

iam fighting with some performance problems on a very simple table which seems to be very slow when fetching data by using its primary key (bigint)

I have this table with 124 million entries:

CREATE TABLE `nodes` (
  `id` bigint(20) NOT NULL,
  `lat` float(13,7) NOT NULL,
  `lon` float(13,7) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lat_index` (`lat`),
  KEY `lon_index` (`lon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and a simple query which takes some id from another table using the IN clause to fetch data from the nodes tables, but it takes like 1 hour only to fetch a few rows from this table. EXPLAIN shows me its not using the PRIMARY key as index, its simply scanning the whole table. Why that? id and the id column from the other table are both from type bigint(20).

mysql> EXPLAIN SELECT lat, lon FROM nodes WHERE id IN (SELECT node_id FROM ways_elements WHERE way_id = '4962890');
+----+--------------------+-------------------+------+---------------+--------+---------+-------+-----------+-------------+
| id | select_type        | table             | type | possible_keys | key    | key_len | ref   | rows      | Extra       |
+----+--------------------+-------------------+------+---------------+--------+---------+-------+-----------+-------------+
|  1 | PRIMARY            | nodes             | ALL  | NULL          | NULL   | NULL    | NULL  | 124035228 | Using where | 
|  2 | DEPENDENT SUBQUERY | ways_elements     | ref  | way_id        | way_id | 8       | const |         2 | Using where | 
+----+--------------------+-------------------+------+---------------+--------+---------+-------+-----------+-------------+

The query SELECT node_id FROM ways_elements WHERE way_id = '4962890' simply returns two node ids, so the whole query should only return two rows, but it takes more or less 1 hour.

Using "force index (PRIMARY)" didnt help, even if it would help, why does MySQL not take that index since its a primary key? EXPLAIN doesnt even mention anything in the possible_keys columns but select_type shows PRIMARY.

Am i doing something wrong?

Upvotes: 1

Views: 1122

Answers (2)

Alepac
Alepac

Reputation: 1831

This is what is called a correlated subquery. You can see this as reference or this popular question posted on Stackoverflow. A better query to use is:

SELECT lat,
       lon
FROM nodes n
JOIN ways_elements w ON n.id = w.node_id
WHERE way_id = '4962890'

Upvotes: 1

Jaydee
Jaydee

Reputation: 4158

How does this perform?

SELECT lat, lon FROM nodes t1 join ways_elements t2 on (t1.id=t2.node_id) WHERE t2.way_id = '4962890'

I suspect that your query is checking each row in nodes against each item in the "IN" clause.

Upvotes: 1

Related Questions