SQL/Database optimization -- table design and queries with 3D data.

[AtlantaBob]

Hi guys,

Many thanks to those who've helped me in previous posts. One more for you. So you know this is all running on a Linux mySQL database on a Celeron server (Dell SC420) w/ 2 GB of RAM.

I've got a many-to-one relationship going using 3 dimensional data (x,y,z) to link two tables.

I see two options when trying to join the data -- one, create some sort of identifier field which concatenates x,y,z data (probably zero padded) and joins on that field. Given the data I'm using, I can't see using anything less than a BIGINT variable to hold that ID.

Otherwise, I can use some subqueries and WHERE tb1.x=tb2.x and tb1.y=tb2.y conditions to try and achieve the same result, but with messier SQL code.

After trying both, it seems like the oneID just causes the server to bog down -- although it seems to be a processor-issue rather than a problem of memory.

For what it's worth, the Many data field will have about 13.5 million records and the One field will have about 300K records.

I sorta know what I'm doing, but I know just enough to be dangerous -- so please don't hesitate offering obvious solutions.

Sorry if I'm being too vague, but it seems like if I post too much info, it's too hard for people to read. Happy to provide more details if necessary.

Thanks!

 

[Crusty]

Can you post your table definitions? ie the output of SHOW CREATE TABLE `table_name`;

Don't forget if you put EXPLAIN in front of a query the query optimizer will spit out what it's going to do to find your data. You can use that to check that your queries are using the right indexes, it'll also explain how it's doing the join and how approximate rows it will be creating before filtering and calculating the result.

 

[PhatoseAlpha]

Do you have to manually build indexes in MySQL?

 

[AtlantaBob]

Crusty,

Thanks for the reply -- I'll do that as soon as I get back in front of the database Monday.

Phatose -

Yes, I believe that you do. (at least in so far as if you don't create them when creating the table, you need to use the following (this is a rough approximation -- I don't have any reference in front of me)

alter table TABLE1 create index INDEX1 using FIELD1;

 

[mugs]

Concatenating the data into one field is a bad idea.

 

[AtlantaBob]

Thanks Mugs -- that was my feeling too -- I mean, I know that part of that is theory, but also, after seeing the initial performance hit....

Sorry for the late reply guys, I couldn't get into the lab yesterday and had to do real work instead. Here's the output of the SHOW CREATE commands for Layers (the table that holds one instance of information for each x,y point) and Data (the table that holds multiple instances of information for each x,y point.) PlanID is essentially my z-variable, and I have been writing queries using an INNER JOIN using PlanID, Where (Data.x=Layers.x AND Data.y=Layers.y) :