Subqery in 15 million table

[Szabi Zsoldos]

Hi everyone,

Trying to do a subquery to select the minimum price from table1, table what holds 15 million rows.
I think that my approach is not the most suitable way of doing this.

I want to select after oen_norm because oen_norm may have 1-10 rows per entry, but I need the smallest price for each oen_norm.

Could you please help on this one ?

I have indexes set up, but that doesnt seem to help for this particular query.

SELECT * FROM db.table1 as a 
WHERE a.quality IN (1,2,3)
AND a.price = (SELECT MIN(price) FROM db.table1 WHERE oen_norm = a.oen_norm)

[smantscheff]

Show the table structure. Show the result of EXPLAIN <your query>. Do you have an index on the price and on the oen_norm field?
Effectively you are grouping on oen_norm, take the minimum price of each group and filter all entries with that minimum price and a quality of 1, 2 or 3. So this might be more efficient:

SELECT prices.oen_norm, min(prices.price) as p, a.id, a.quality
FROM table1 as prices, table1 as a
GROUP BY prices.oen_norm
WHERE prices.p = a.price
AND prices.oen_norm = a.oen_norm
AND (a.quality = 1 OR a.quality = 2 or a.quality = 3)

[Szabi Zsoldos]

Thank you for looking into this one.

No, i have no index on the price but only on oen_norm.

So here are the columns with the additional info.

"Field" "Type"  "Null"  "Key"   "Default"   "Extra"
"id"    "int(20) unsigned"  "NO"    "PRI"   ""  "auto_increment"
"operation" "varchar(5)"    "YES"   ""  ""  ""
"aen"   "varchar(50)"   "YES"   ""  ""  ""
"oen_norm"  "varchar(50)"   "NO"    "PRI"   ""  ""
"description"   "varchar(50)"   "NO"    "PRI"   ""  ""
"price" "float(10,2) unsigned"  "YES"   ""  ""  ""
"currency"  "enum('RON','EURO','HUF','USD')"    "YES"   ""  ""  ""
"supplier"  "char(7)"   "NO"    ""  ""  ""
"manufacturer"  "varchar(30)"   "YES"   ""  ""  ""
"quality"   "tinyint(4)"    "NO"    "PRI"   "0" ""
"mfcodeax"  "tinytext"  "YES"   ""  ""  ""
"mfname"    "varchar(50)"   "YES"   "MUL"   ""  ""
"deliverydays"  "varchar(2)"    "NO"    "PRI"   ""  ""
"mfrnr" "int(10)"   "NO"    "PRI"   ""  ""
"ts"    "timestamp" "YES"   ""  ""  ""

And here is the explain query.

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "a" "index" ""  "PRIMARY"   "117"   ""  "14672148"  "Using index; Using temporary; Using filesort"

The above query is not working, I think that is because of prices.p, i'ts kinf of unknown and invalid use of group functions.

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE prices.p = a.price
AND prices.oen_norm = a.oen_norm
AND (a.quality = 1 O' at line 4 */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec. */

[smantscheff]

No. prices is not unknown because it is defined in the AS clause. The error is that I put the GROUP BY clause before the WHERE clause. So switch them.

[Szabi Zsoldos]

Switched them :)

/* SQL Error (1054): Unknown column 'prices.p' in 'where clause' */

[smantscheff]

You should know better than to test code in your database which you do not understand. Suppose I would suggest that you enter the query

DELETE * from table1;

Would you?

Now try:

EXPLAIN
SELECT prices.oen_norm, min(prices.price), a.id, a.quality
FROM table1 as prices, table1 as a
WHERE prices.price = a.price
AND prices.oen_norm = a.oen_norm
AND (a.quality = 1 OR a.quality = 2 or a.quality = 3)
GROUP BY prices.oen_norm

Then add an index on the price field and try again.

[Szabi Zsoldos]

Well sometimes it happens that I forgot to use explain or properly test all the querys for optimization :)

Anyway, thank you for the help!