Problems With Sqlwhere Command

[thomas80]

hello.

I have a short question.

I'm testing the sqlwhere command to optimize the synchronisation.

Example: I have one table with measured values

The tool need 59 second to syncronisate 169514 rows.

If I'm using the sqlwhere command I need the same time. 🙁

In my test condition in the schedule have only 10 rows with ID's higher than 5020.

But the schedule need the same time 🙁

MEAS_TRANSACTION_ID >5020

I tried it with some other test conditions but allways the same?

Can anybody help me please?

That is wrong?? Why I need always the same time. With and without sqlqhere?

Thanks for all.

Best Regards

Thomas …

[peterlaursen]

Do you have an index on the column?

[thomas80]

hey Peter,

Thanks for you answer.

What do you mean with index?

Sorry for the stuped question. I'm a newbie in this field 🙁

In the attachment are a screen of my table.

My goal is to check it with the timestamp.

I tested at first with “MEAS_TIMESTAMP>current_timestamp-10000”

I'm using the SQLyog Database Synchronization Wizard for the configuration.

I hope you can help me.

Thanks.

Best Regards Thomas

[peterlaursen]

Well ..

1) about indexes in general http://en.wikipedia….Index_(database): “A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.”

2) about indexes in MySQL specifically: http://dev.mysql.com…ql-indexes.html

Let us take an analogy: suppose you have 20000 paper sheets. Now you want to find all sheets with your name on top. The sheets are not sorted in that order. You have check each and every sheet. Using an index on the names on top you would easily find that the sheets with your name if the index told that they were sheet no. 456, 12224 and 17689. It is similar with SQL: If no index exsits on the column(s) used in the WHERE-clause the server will need to read every row of data (called a 'full table scan'). If an index exists the server will only need to scan the (usually much smaller) index to locate the data. Or just think about an ordinary book having an index. If you are looking for some specific information you would not start reading the complete book, but first try if the index tells you what pages to read.

In the INFO tab in SQLyog you can see what indexes you have (or you may execute “SHOW CREATE TABLE table_name;”).

Please try from the table (context) menu to open the 'manage indexes' dialog. and add an index on the column you use in the WHERE-clause.

[Author]

Posts