rebuild database to decrease it's size

[nego78]

i've got many tables but to describe my problem i think that will be enough.

CREATE TABLE Elementy (
  Elementy_Id varchar(20) NOT NULL default '',
  Elementy_Dane text,
  PRIMARY KEY (Elementy_Id)
);

CREATE TABLE Linki (
  Linki_Id mediumint(10) unsigned NOT NULL auto_increment,
  Telefony_Id smallint(5) unsigned NOT NULL default '0',
  Modele_Id int(10) unsigned default NULL,
  Uslugi_Id int(10) unsigned default NULL,
  Kategorie_Id int(10) unsigned default NULL,
  Telefony_Link varchar(50) collate utf8_polish_ci default NULL,
  Modele_Link varchar(50) collate utf8_polish_ci default NULL,
  Uslugi_Link varchar(50) collate utf8_polish_ci default NULL,
  Kategorie_Link varchar(50) collate utf8_polish_ci default NULL,
  PRIMARY KEY  (Linki_Id),
  KEY IX_LnTel (Telefony_Link),
  KEY IX_LnMod (Modele_Link),
  KEY IX_LnUsl (Uslugi_Link),
  KEY IX_LnKat (Kategorie_Link)
);
CREATE TABLE LinkiDoElementow (
  Linki_Id mediumint(10) unsigned NOT NULL default '0',
  Elementy_Id varchar(20) collate utf8_polish_ci default NULL,
  KEY Linki_Id (Linki_Id)
);

Table Elementy has about 100 000 rows
Table LinkiDoElementow has 38 000 000 rows
Table Linki has about 260 000 rows

What i do:
I select From Linki Linki_Id on special WHERE clause which depends on URL:

SELECT Linki_Id FROM Linki WHERE Telefony_Link = 'nokia' AND Modele_Link = 'n73' AND Uslugi_Link = 'dzwonki' AND Kategorie_Link = 'nowosci'

(I extract also Telefony_Id,Modele_Id,Uslugi_Id,Kategorie_Id and do inner joins to other tables to extract some data but it's irrelevant)

Then i Do a select with inner join from LinkiDoElementow
joined with Elementy.

Now i need to decrease size of LinkiDoElementow.
I thought to add a column Kategorie_Id to Elementy, create table

CREATE TABLE ModUslDoElementow (
  Elementy_Id varchar(20) NOT NULL default '',
  KEY IX_El (Elementy_Id)
  KEY IX_ModUsl (Modele_Id,Uslugi_Id)
);

And insert only unique rows from joinded Linki AND LinkiDoElementow
And Remove Linki_Id from Linki (i won't need it)
Linki i use only to retrieve ids of Telefony_Id,Modele_Id,Uslugi_Id,Kategorie_Id

and later after selecting FROM Linki i can do another
query

SELECT Elementy... FROM ModUslDoElementow AS mude INNER JOIN  Elementy AS e ON e.Elementy_Id=mude.Elementy_Id AND e.Kategorie_Id = X
WHERE mude.Modele_Id = A AND mude.Uslugi_Id = B

but i'm afraid that this query won't be fast enough


ps. Elementy_Id is varchar because data can be numeric and letters

 

[DonQuichote]

First, I would never write a textual primary key. That suggests that you are using a data field as an identity. Never do that. The fact that you can find a record by name or code, does not make it optimal to link to it using that name. So in your first table, I would add an autonumber primary key identity field (and change the existing primary key into just a unique index).

Furthermore, don't be afraid. Investigate. Use the EXPLAIN command to see how this query will be optimized.

From the top of my head, the WHERE clause suggests that only a few rows of the mule table will be considered before the join is performed. If this is the case, that query will not really be slow.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)