Mass query lag when linking into unreferenced table

[nik0n]

Ok, I so I have a query such as this:

 

SELECT ic_data.bucket FROM ic_data, ic_index_cities WHERE ic_data.csv=1 OR ic_data.csv=2 OR ic_data.csv=3 OR ic_data.csv=4 OR ic_data.csv=5 OR ic_data.csv=6 OR ic_data.csv=7 OR ic_data.csv=8 OR ic_data.csv=9 OR ic_data.csv=10 OR ic_data.csv=11 OR ic_data.csv=12 OR ic_data.csv=13 OR ic_data.csv=14 OR ic_data.csv=15 OR ic_data.csv=16 OR ic_data.csv=17 OR ic_data.csv=18 OR ic_data.csv=19 AND value GROUP BY ic_data.bucket ORDER BY SUM(ic_data.bucket);

 

It, for some reason, takes 150 seconds to process. Now, if I remove "ic_index_cities" from the FROM clause, processing time plummets to 0.15 seconds, however I need to reference this table (although I removed the references above).

 

What I'm saying is, if I put the table in the FROM clause, whether I reference it in the SELECT or WHERE clauses or not, it still causes horrible lag. I can also remove the "ORDER BY" clause with no reduced processing time. Does anyone know why this is?

[fenway]

First, use an IN clause, or BETWEEN.

 

Second, you never use ic_index_cities -- and you're not joining it properly either -- please clarify.

[nik0n]

Greetings,

 

mysql --help first line output: mysql  Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (x86_64) using readline 5.0

 

MySQL SHOW CREATE TABLE ic_data:

mysql> SHOW CREATE TABLE ic_data;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ic_data | CREATE TABLE `ic_data` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `csv` int(10) unsigned NOT NULL,
  `bucket` varchar(4) NOT NULL,
  `city` varchar(4) NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  USING BTREE (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=77176 DEFAULT CHARSET=latin1 COMMENT='icTraffic: Data' |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

mysql> SHOW CREATE TABLE ic_index_cities;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                       |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ic_index_cities | CREATE TABLE `ic_index_cities` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `desc` varchar(100) NOT NULL,
  `csv` int(10) unsigned NOT NULL,
  `sid` varchar(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1834 DEFAULT CHARSET=latin1 COMMENT='icTraffic: City Index' |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

EXPLAIN query output for aforementioned query:

mysql> EXPLAIN SELECT ic_data.bucket FROM ic_data, ic_index_cities WHERE ic_data.csv=1 OR ic_data.csv=2 OR ic_data.csv=3 OR ic_data.csv=4 OR ic_data.csv=5 OR ic_data.csv=6 OR ic_data.csv=7 OR ic_data.csv=8 OR ic_data.csv=9 OR ic_data.csv=10 OR ic_data.csv=11 OR ic_data.csv=12 OR ic_data.csv=13 OR ic_data.csv=14 OR ic_data.csv=15 OR ic_data.csv=16 OR ic_data.csv=17 OR ic_data.csv=18 OR ic_data.csv=19 AND value GROUP BY ic_data.bucket ORDER BY SUM(ic_data.bucket);
+----+-------------+-----------------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-----------------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | ic_index_cities | index | NULL          | PRIMARY | 4       | NULL |  1833 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | ic_data         | ALL   | NULL          | NULL    | NULL    | NULL | 77175 | Using where                                  |
+----+-------------+-----------------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

 

I am aware that the query above joins to ic_index_cities but never references it.

The thing is I do need to reference it, however this in itself isn't important.

 

What I'm trying to make apparent is the fact that the referencing of the table is

not what is causing the lag; it has something to do with the way I am joining.

 

You say I was not properly joining; I read from various sources that in order to do a JOIN

you can either use a "JOIN" keyword or you can simply crossreference tbl1.x=tbl2.y, it's

just a question of style. Could you please provide me with an example of what a proper JOIN

would be?

 

Thank you for the advice regarding IN or BETWEEN. However even if all these ic_data.csv=*

clauses are removed the lag still persists.

 

ic_index_cities: 1833 rows

ic_data: 77175 rows

 

I don't see why linking into a table with 1833 rows without even referencing it should be causing

this much query lag..  ???

[nik0n]

well i apparently am unable to edit my post but i would just like to update it:

the query is actually exactly this:

 

mysql> EXPLAIN SELECT DISTINCT ic_data.bucket FROM ic_data, ic_index_buckets, ic_index_bucketgroups WHERE ic_index_buckets.sid=ic_data.bucket AND ic_data.csv='3' OR ic_data.csv='4' OR ic_data.csv ic_data.csv='8' OR ic_data.csv='9' OR ic_data.csv='10' OR ic_data.csv='11' OR ic_data.csv='12' OR ic_data.csv='13' OR ic_data.csv='14' OR ic_data.csv='15' OR ic_data.csv='16' OR ic_data.csv='17' OR ic_data.csv='20' OR ic_data.csv='21' AND ic_index_buckets.csv=ic_data.csv AND value GROUP BY ic_data.bucket ORDER BY SUM( ic_data.value ) DESC;
+----+-------------+-----------------------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table                 | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-----------------------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | ic_index_bucketgroups | index | NULL          | PRIMARY | 4       | NULL |     5 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | ic_index_buckets      | ALL   | NULL          | NULL    | NULL    | NULL |  2057 |                                              |
|  1 | SIMPLE      | ic_data               | ALL   | NULL          | NULL    | NULL    | NULL | 77175 | Using where                                  |
+----+-------------+-----------------------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
3 rows in set (0.00 sec)

 

[fenway]

I tried to re-write your query:

SELECT 
DISTINCT ic_data.bucket 
FROM 
ic_data
INNER JOIN ic_index_buckets ON (ic_index_buckets.sid=ic_data.bucket AND ic_index_buckets.csv=ic_data.csv )
INNER JOIN ic_index_bucketgroups ON ( ..... )
WHERE ( ic_data.csv BETWEEN 3 AND 4 OR ic_data.csv BETWEEN 8 AND 17 OR ic_data.csv BETWEEN 20 AND 21 )
AND value ( = ????? )
GROUP BY ic_data.bucket 
ORDER BY SUM( ic_data.value ) DESC

I'm confused by a few things:

 

1) Why is ic_index_bucketgroups used? How is it related to the other tables?

2) you're not checking "value" for anything in the where clause?

3) Why GROUP BY / ORDER BY if you're using DISTINCT with a single column!

4) There are many missing indexes.