urgent sql solution

[Anon]

Hello, I am changing from MS-SQL/ASP to MySQL/PHP a search engine and I have problems with SQL statements:

In MSSQL I have queries like this:

SELECT Categorie.ID_Categ, Categorie.DescrCateg FROM Categorie INNER JOIN (CommercioCategorie INNER JOIN CommercioLocalita ON CommercioCategorie.ID_Comm=CommercioLocalita.ID_Comm) ON Categorie.ID_Categ = CommercioCategorie.ID_Categ
WHERE CommercioLocalita.ID_Frazione=1001

I try to write this using JOIN condition but also changing JOINS into WHERE conditions:
SELECT Categorie.ID_Categ, Categorie.DescrCateg FROM Categorie, CommercioCategorie, CommercioLocalita WHERE
Categorie.ID_Categ=CommercioCategorie.ID_Categ AND CommercioCategorie.ID_Categ=CommercioLocalita.ID_Categ
AND CommercioLocalita.ID_Frazione=1001

All tables are indexed and all fields joined are keys used to link tables in two examples posted here, but queries are too slow... for a search engine about 15 seconds with 60,000 records means very slow :-((

[lawnmowerman]

You may want to add indexes to the ID_Categ and ID_Frazione columns if you don't already have them.

You said the tables were indexed, but you didn't say how.

This article: http://www.phpbuilder.com/columns/tim20010110.php3
has a great overview of how MySQL indexes need to be created for best performance for different queries.

If your query is structured so that it tries to find an index with certain columns, it won't find/use the index if the columns are in a different order.

Essentially, if you create an index wth:
CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);

If I recall correctly, MySQL will use this index for:

SELECT * FROM mytable WHERE category_id > 3 AND user_id = 5;

but will NOT use this index for:

SELECT * FROM mytable WHERE user_id = 5 AND category_id > 3;

This is due to the fact that the index you created sorts the table by category_id first, then user_id. By reversing the order of those 2 columns in the WHERE statement, MySQL looks for an index on user_id and category_id (instead of category_id and user_id).

Hope this helps.

-Rich

[Anon]

Thank you Rich...
so you are telling me that the problem JOIN/WHERE is not relevant if the database structure is correctly indexed?

If I use different key-index for each query of the search engine, I suppose I must create a great number of indexes.... so INSERT/UPDATE sql command are slower...is it rigth?

[lawnmowerman]

Right...if the database is correctly indexed and your queries are properly structured to use the indexes, your queries will be much faster (at the cost of insert and update time, but most databases are read MUCH more than updated).

If you read up some more on indexes you will find that you can probably build a a few indexes for most of your queries. I'm not sure if this was in Tim's article I mentioned in my last reply or not.

Let's say you have a table User with columns userID, userName, userPass, and userEmail. If you plan on searching by userName in one query and userName AND userEmail in another query, you can use 1 index:

CREATE INDEX User_userName_userEmail on Users (userName, userEmail);

These selects will use the index:

SELECT * FROM User WHERE userName LIKE '%root%';
SELECT userPass FROM User WHERE userName = 'root';
SELECT userName FROM User WHERE userName LIKE '%test%' AND userEmail LIKE 'test@%';
SELECT userName FROM User WHERE userName = 'root' AND userEmail LIKE 'root@%' AND userPass = 'stayout';

But these selects will NOT use the index:

SELECT from User WHERE userEmail LIKE 'test@%' AND userName LIKE '%test%';
SELECT from User WHERE userEmail = '[e-mail]';

The 'where' clauses must be in the same order as the order the index was created, starting from the left to use an index.

I hope I didn't get anything wrong here...I'm pretty sure this is right.

-Rich