Selecting last number of MySQL rows

[PeterG]

I do not know if this is the crrect forum for this and I apologize it this is the case.
This is the Script that I have to select all records from a table.
put "SELECT * FROM " & tTableName into tSQL
It slows the process as there are 260 000 records in the table.
I want to use this select statement to select the last number of rows:

SELECT * FROM (
SELECT * FROM table delegates BY DELEGATEOID DESC LIMIT 50
) sub
ORDER BY DELEGATEOID ASC
At the moment I am still trying to get my head arround the syntax of LiveCode scripting.
Any suggestions would be appreciated.
Thanks
Peter G

[dunbarx]

Probably I am missing what you are asking, in that there is something particular in your query about mySQL, of which I am no expert.

But if you have read from the database, and have the information within LiveCode, that data already residing in a variable or array, what are you really asking? To select certain lines (records) in that data set? What do you mean by "selecting"? Do you mean to extract certain data based on some criteria ? Is the "script" in your post pseudocode?

Write back...

Craig Newman

[Klaus]

Hi Peter,

also not sure I understand what you are after, but can't you:
...
SELECT COUNT(*) FROM yourDBhere
## = the number of records
...
And then use this number to get the last record?


Best

Klaus

[dave_probertGA6e24]

Or if you have a unique autoincrement field (eg. ref) then you could do "SELECT max(ref) from tablename" which would give you the exact row reference.

Cheers,
Dave

[PeterG]

Hi all

The scrpt that I have is from the App Academy examples(Dispay a SQL Datase)
Basicaly what I need is for the script to pull only the last say 100 records instead of pulling all the records.
The SQL select statement I sourced from a MySQL site as an example to limit the amount of records selected.
Craig this is the bit that puts the records in the variable if I understand it correctly. The problem with this that it takes to long to select all the records By limiting the records will speed up the process. The user will only require to see the last number of records.


Many thanks,
Peter G

[dunbarx]

Hi.

So the issue is to only pull a certain group of records from the database. I thought that there were powerful and speedy queries that made this a snap.

Because once the data, or any part of it, is in LC, there is no issue, pulling any subset of that data set will be very fast.

My point is that I thought the whole point of using SQL or any of its ilk was that data input and output was fast, easy and comprehensive.

Or do I have this wrong, and your speed issues are wholly within LC?

EDIT:

I wrote a quick script that loaded a field with 260,000 lines of data, several text chunks separated by tabs in each line. I then extracted only those lines that met a certain criterion. I got 37,000 lines that did, and it took 9 ticks.

Craig Newman

[PeterG]

Hi Craig
My issue is definitely not with LC it is just a practical thing and I apologize for not being clear.
I am just following the advice that I have read on working with large MySQL datasets.
My select took about 56 seconds but then again I am siting in darkest Africa LOL.
As you will notice in my 1st post I do have the script and tested it.
And here it is again:
"SELECT * FROM (
SELECT * FROM table delegates BY DELEGATEOID DESC LIMIT 50
) sub
ORDER BY DELEGATEOID ASC"
And here is the example with the select statement from LC Academy that selects all records:
"put "SELECT * FROM " & tTableName into tSQL
My question is: How do I change the example script which selects all records to accommodate my MySQL select statement that will select the last 50.

Thanks,
Peter G

[Simon]

First off I know nothing about mysql but from a quick search I found the word to be "Limit" e.g.
SELECT * FROM `your_table` LIMIT 0, 10
google "mysql select limit example"

Once again maybe I'm just making noise.

Simon

[SparkOut]

You're trying to replace the SQL query in the variable tSQL from the Academy with the more complex query you have already determined will help speed data retrieval right?
Just put what you want into the variable, such as:

Code: Select all

put "SELECT * FROM (SELECT * FROM" && tTableName && "BY DELEGATEOID DESC LIMIT 50) sub ORDER BY DELEGATEOID ASC" into tSQL

The tSQL variable is only a placeholder for whatever query you want the engine to submit.

[PeterG]

Hi

Many thanks,
Just what I was looking for.
Much appreciated.

Peter G