So I have a class that creates a table to be populated with data. Right now I have all the column names the same (product_name, date, etc). I noticed that when I view my tables in Webmin that there is only one index named "product_date" despite the fact that there are, supposedly, two tables using the index. I don't think this can be good.
My question is whether or not this will cause a conflict in the future? I don't want to populate the tables with thousands of rows if I'm only going to need to restructure everything later. I can't imagine that I'm the first to encounter this... maybe I'm just misinformed on how indexes work/webmin displays indexes and being overly paranoid.
(edit)
In response to one comment below, here are the results of SHOW CREATE TABLE tablename:
c_1 | CREATE TABLE c_1 (
p_id int(11) NOT NULL auto_increment,
nm varchar(100) NOT NULL,
m_name text NOT NULL,
PRIMARY KEY (p_id),
KEY nm (nm),
FULLTEXT KEY m_name (m_name)
) ENGINE=MyISAM DE开发者_开发百科FAULT CHARSET=latin1
c_2 | CREATE TABLE c_2 (
p_id int(11) NOT NULL auto_increment,
ne varchar(100) NOT NULL,
m_name text NOT NULL,
PRIMARY KEY (p_id),
KEY nm (nm),
FULLTEXT KEY metaphone_name (m_name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Note that all the indexes on equivalent columns are named the same way.
If it's an index per table, no problem
If I understand your question correctly (a big if), you must create an index for each table. Indexes do not cover more than one table until you get into advanced concepts like indexed/materialized views, which I don't think MySQL handles.
精彩评论