Optimizing sql query

Yii 1.1 General Discussion
1

Greetings!

I have a big doubt i would like to solve this.

I have a table in DB oracle with like 1million entries. The most important columns are the ID (primary key) and CREATE_TIMESTAMP. This last column is timestamp(6).

What i want to do is a query that returns the latest 20 rows that were created, optimized…

i tried this queries:




select * from async_events

WHERE CREATE_TIMESTAMP >= trunc(sysdate)-1 and CREATE_TIMESTAMP <= trunc(sysdate) and rownum<=5







select * from async_events

where CREATE_TIMESTAMP >= CURRENT_TIMESTAMP(6) - interval '5' day

this queries works, but they take at least 2minutes because they don’t avoid the exausting search in all rows.

Any tips i would apreciate :)

2

Have you create an index on CREATE_TIMESTAMP field?

3

I thought of that already, but I can’t change the model of DB. I have no authorization for that. This optimization can’t be at DB level :(

4

You can try selecting last 20 records by ID (int) and only then testing the timestamp, it may be faster:

select * from (select * from async_events order by ID desc limit 20) t where t.CREATE_TIMESTAMP <= …

(not tested, replace * with required fields)

5

SELECT * FROM [color=#1C2837][size=2]async_events ORDER BY create_timestamp DESC LIMIT 20;[/size][/color]

6

Thank you for your replys…

PoL -> your query works but it takes more than 2 minutes because the LIMIT doesnt avoid the search of all rows. First it gets all rows ordering by timestamp, then it returns the rows with limitation number we said.

rosoft2001 -> That would be perfect if the primary key is a SEQUENCE number. But it is not… I dont know how to get the latest inserted rows with a random primary key :(

7

Hi Marcolini

I can only come upon some hacks for solutions, which might work or not depending on your system, but surely are not the most elegant solutions in the world:)

  • If you control the creation of the new records, you could put a copy of all new records in another db/table, where you control the table type and structure and can make indexes. So this table to could serve only for the purpose of querying newly added records.

  • If there is no deletions/updates of records in the table that you query AND if you query the table more often than records are added. You might save the 20 records + a Count of records, and then start out with a COUNT query to see if new records where added. If not you can use the saved records.

  • if it is not needed to have completely updated data, you might make a copy of the whole table at some interval (fx once a day) and query that. (off course the copy should be in a faster table-structure).

Hope that can help.

Best,

Sune

8

hey Sune!!

Thank you for your reply and extended explication :)

Your solution is well fancy and explained. But im not able to implement it. This project is from my company and im not able to create/alter tables or something about Database level.

Even more i dont know if there is a solution for my problem with my limitations… maybe i have to bother my boss to treat this in DB :(

9

Hacky solution, but you can put in a parameter containing a specified minimum id.

Then add to your sql query:




select * from async_events

where CREATE_TIMESTAMP >= CURRENT_TIMESTAMP(6) - interval '5' day

AND id > {specified_min_id}

This can be put anywhere. My suggestions would be Yii::app()->params, local file, or the global state.

The latter two are better because you can update them automatically.

10

I agree.

It’s your boss’es business, not yours. Just report him the problem.

Your boss won’t be glad to see your tricky work arounds on the program level before he reconsider the db design.