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
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
