I have a general SQL structure question for you, in regards to MyISAM tables.
I have three tables. They are Vendor (81 rows), Product (427 rows) and Version (477 rows).
(Dual-field primary-key required due to DB replication)
--<Table Information>
---------------------------------
VENDOR
uid (smallint 5) pk
serv_id (char 1) pk
company_name (varchar 50)
valid (tinyint 1)
---------------------------------
PRODUCT
uid (mediumint 8) pk
serv_id (char 1) pk
vendor_id (varchar 6)
software_name (varchar 50)
valid (tinyint 1)
---------------------------------
VERSION
uid (mediumint 8) pk
serv_id (char 1) pk
product_id (varchar 9)
version_name (varchar 10)
valid (tinyint 1)
---------------------------------
--<Dependencies>
PRODUCT.vendor_id=concat(VERSION.serv_id,VERSION.uid)
VERSION.product_id=concat(PRODUCT.serv_id,PRODUCT.uid)
I'm trying to generate a listing of all combinations. Both SQL queries are taking about 6 seconds to run.
--<1st Query>
select v.company_name,p.software_name,s.version_name
from
vendor v left join product p on p.vendor_id=concat(v.serv_id,v.uid)
left join version s on s.product_id=concat(p.serv_id,p.uid)
where
v.valid=1 and p.valid=1 and s.valid=1
--<2nd Query>
select v.company_name,p.software_name,s.version_name
from
vendor v,product p,version s
where
p.vendor_id = concat(v.serv_id,v.uid)
and s.product_id=concat(p.serv_id,p.uid)
and v.valid=1 and p.valid=1 and s.
Here is the Explain from the two queries. Looks like the query is looking through 16,497,999 possible rows. I'm thinking 6 seconds is pretty good for sixteen-million rows, but do you know how I can improve upon either of the queries?
I really need to get the query run in 1/6 the time.
'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra'
'1','SIMPLE','v','ALL','[NULL]','[NULL]','[NULL]','[NULL]','81','Using where'
'1','SIMPLE','s','ALL','product_id,product_id_2','[NULL]','[NULL]','[NULL]','477','Using where'
'1','SIMPLE','p','ALL','vendor_id,vendor_id_2','[NULL]','[NULL]','[NULL]','427','Range checked for each record (index map: 0x6)'
'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra'
'1','SIMPLE','v','ALL','[NULL]','[NULL]','[NULL]','[NULL]','81','Using where'
'1','SIMPLE','s','ALL','product_id,product_id_2','[NULL]','[NULL]','[NULL]','477','Using where'
'1','SIMPLE','p','ALL','vendor_id,vendor_id_2','[NULL]','[NULL]','[NULL]','427','Range checked for each record (index map: 0x6)'
Thanks in advance,
-Brett