Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Aggregate field or index to optimize queries

Status
Not open for further replies.

chpicker

Programmer
Apr 10, 2001
1,316
I'm trying to migrate a database I designed in MS SQL Server last year to MySQL. One thing that I had set up in MSSQL was a table that contained an aggregate field which was then indexed. Here are the relevant fields from my table:

MACHINEID int unsigned NOT NULL
ISNEW bool NOT NULL

In MSSQL I set up an aggregate field called MACHINENEW which was defined as MACHINEID * ISNEW. I then created an index on MACHINENEW.

The purpose behind the aggregate field was to provide an index that would give me all of the new items for a specific machine. In essense, this field contains the machine number if it is new and 0 if it is not. Any given machine can simply execute a query "WHERE MACHINENEW=MyNumber" to get its new records, then SET ISNEW=0 with the same WHERE clause to prevent them from appearing next time.

I cannot figure out how to do this in MySQL. I tried a combined index (MACHINEID,ISNEW), but my queries are extremely slow. How would I go about wording a query's WHERE clause to take advantage of this index?

If that doesn't work, is there a way to create a column in a table that is automatically calculated based on 2 or more other columns so I can index on that column?

This table has millions of records in it, and the query pulls at most 2000 records at a time. Most of the time there will be 0. An index on either field alone will not be sufficient as each could return hundreds of thousands of records. Any suggestions?

Stored procedures are not an option at this time since I am using MySQL version 4.1.11.

The only other alternative I can think of is to create my own quasi-aggregate column which my program would have to always keep updated. This would require a MAJOR overhaul of the program and is something I would very much like to avoid.

Ian
 
i believe the term is computed column, not aggregate (aggregate refers to the result of a function that operates on a column of values)

instead of MACHINEID and ISNEW, try MACHINEID and NEWMACHINEID, such that NEWMACHINEID will contain MACHINEID if it's new, and NULL otherwise

so searching for new machines, you'd say

... where NEWMACHINEID is not null

i'm not totally sure the index on NEWMACHINEID will perform successfully, but you gotta try it, eh

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top