[Phpmyadmin-devel] Zoom-search and initial data reading

[Piotr Przybylski]

2011/8/4 Ammar Yasir <ayax88 at gmail.com>:
>
>
> On Thu, Aug 4, 2011 at 5:01 PM, Piotr Przybylski <piotr.prz at gmail.com>
> wrote:
>>
>> 2011/8/4 Marc Delisle <marc at infomarc.info>:
>> > Piotr Przybylski a écrit :
>> >> 2011/8/4 Ammar Yasir <ayax88 at gmail.com>:
>> >>>
>> >>> On Thu, Aug 4, 2011 at 2:01 AM, Marc Delisle <marc at infomarc.info>
>> >>> wrote:
>> >>>> Ammar Yasir a écrit :
>> >>>>>
>> >>>>> On Wed, Jul 27, 2011 at 10:22 PM, Ammar Yasir
>> >>>>> <ammaryasir.88 at gmail.com
>> >>>>> <mailto:ammaryasir.88 at gmail.com>> wrote:
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>>     On Wed, Jul 27, 2011 at 3:37 PM, Marc Delisle
>> >>>>> <marc at infomarc.info
>> >>>>>     <mailto:marc at infomarc.info>> wrote:
>> >>>>>
>> >>>>>         Ammar,
>> >>>>>         With Firebug I had a look at the network traffic when I
>> >>>>> click a
>> >>>>> data
>> >>>>>         point to edit it: I was surprised to see none.
>> >>>>>
>> >>>>>         IMO this is not good: it means that all the columns for all
>> >>>>> rows
>> >>>>>         are in
>> >>>>>         memory, making the browser able to handle far less rows.
>> >>>>>
>> >>>>>         Is there a reason why you are reading the complete rows to
>> >>>>>         generate the
>> >>>>>         plot? I expected that you would just read the necessary
>> >>>>> columns,
>> >>>>>         then
>> >>>>>         use AJAX to read a complete row when the user wants to edit
>> >>>>> it.
>> >>>>>
>> >>>>>     I'm currently working on the edit feature for strings. I'll work
>> >>>>> on
>> >>>>>     it after this.
>> >>>>>
>> >>>>>         --
>> >>>>>
>> >>>>> Implemented this and pushed to my repo. I only send the xField,
>> >>>>> yField
>> >>>>> and dataLabel to the user now.
>> >>>> Ammar,
>> >>>> you have reduced the amount of data transferred between the PHP level
>> >>>> and the Javascript level, by sending less data in the querydata div.
>> >>>>
>> >>>> Can you also reduce what is transferred between the MySQL server and
>> >>>> the
>> >>>> web server, by avoiding to generate "SELECT *" in the query
>> >>>> generation
>> >>>> part, instead selecting only the needed columns?
>> >>>>
>> >>> That would have been the ideal case but to generate the
>> >>> unique-condition(
>> >>> function PMA_getUniqueCondition($handle, $fields_cnt, $fields_meta,
>> >>> $row,
>> >>> $force_unique=false) ), I need the complete row. So I cannot do much
>> >>> about
>> >>> the query generation on the server side other than putting a limit on
>> >>> the
>> >>> number of rows retrieved.
>> >>>
>> >>
>> >> It will increase complexity o bit but you can do one of the two:
>> >> - select one full row with "SELECT * FROM ... LIMIT 1" (any full row
>> >> will do so no ORDER here) and analyze which rows you need by looking
>> >> for primary/unique key fields,
>> >> - if you have only one table you can analyze SHOW CREATE TABLE
>> >> statement and look for PRIMARY KEY and UNIQUE constraints
>> >
>> > But PMA_getUniqueCondition() also handles the case when there is no
>> > unique key.
>>
>> Yes, but in most cases there is one. If there is no unique key then
>> 'SELECT *' is needed, but if there is one then the built query can
>> return smaller result, without useless columns.
>>
> I actually had started my implementation like piotr has described but came
> across the PMA_getUniqueCondition() function later so went ahead with using
> that. The server side load can be acceptable I guess compared to what I
> transfer to the Javascript level ?
>

I believe it is acceptable. The only use case in which it makes
difference is when you have large LOB columns in the table and are
buffering MySQL response (using PMA_DBI_QUERY_STORE instead of
PMA_DBI_QUERY_UNBUFFERED or cache'ing result in any way), because then
you can very quickly get out of memory.

-- 
Regards,
Piotr Przybylski